Hello,

I have the following problem:

Place a TADOQuery on a form with the following SQL:

SELECT accounts.aid, Count(*) AS [count], accounts.costpersheetperday,
accounts.amount, accounts.creditlimit,
accounts.accountname, accounts.email, accounts.invoicedetails ,
accounts.prepay, accounts.lastchargedon, accounts.lastinvoicedetails
FROM accounts INNER JOIN sheets ON accounts.aid = sheets.aid
WHERE (((sheets.CreationDate)<Now()-1))
GROUP BY accounts.aid, accounts.costpersheetperday, accounts.amount,
accounts.creditlimit, accounts.accountname, accounts.email,
accounts.invoicedetails, accounts.prepay, accounts.lastchargedon,
accounts.lastinvoicedetails

The query is designed to count the number of sheet records per account while
also returning some account fields hence the grouping.

Now I place a Datasource and a DBMemo control on the form. hook the whole
thing up and assign the memo field called invoicedetails to the DBMemo's
datafield property. The field contains more that 255 characters in the
database but the DBMemo only shows 255 characters!

If I tried it many other ways (runtime query creation using FieldbyName
casting it to TMemoField) it still returns only 255 char.

However if I use a simpler (but useless) query such as

Select * from accounts

then there is no problem. I really want to avoid having to run more than one
query if I can. Can anyone help me on this one please?

Thanks

Paul van Dinther

When you use a Group by clause the query enforces uniqueness in all the fields
in the group by clause.  Many database drivers either do not allow memo fields
in a group by or truncate the memo value to 255 characters to compare
uniqueness.

You can try rewriting the query to use a subquery to do the grouping

SELECT select D1.*, D2.[count]
FROM accounts  D1 INNER JOIN
(Select Aid, count(*) as [Count] from sheets
WHERE (((sheets.CreationDate)<Now()-1))
Group by Aid) D2
ON D1.aid = D2.aid

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Brilliant, that did the trick! Not only complete memo data but also a live
query as I can update straight back into the data set.

Thanks a lot, learned something again :-)

Paul van Dinther

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:21nnevkuorjlhu5t8l6eq7rju49snrbtcd@4ax.com...