SQL Statement - memo field 255 characters only

[bluegenel]

Hi

It's me again.

I have two Tables in an Access 2000 database, Table1 and Table2, which is part of an asp application.

The following SQL statement displays the questions from Table1 and does a count on Table2.id_no, which works fine.

The problem is the 'comment' field is only displaying 255 characters as if it was a text box type field in the Access database. The 'comment' field is definitely a memo field and when I change the SQL statement to a more basic one I don't have this problem.

Here is the SQL statement:

strSQL = "SELECT COUNT(Table2.id_no) AS CountOfid_no, Table1.id, Table1.name, Table1.time, Table1.ampm, Table1.maindate, Table1.longdate, Table1.av, Table1.comment, Table1.ipadd, Table1.replies, Table1.replydate, Table1.replytime, Table1.replyampm, Table1.total, Table1.browser FROM Table1 LEFT JOIN Table2 ON Table1.id=Table2.id_no GROUP BY Table1.id, Table1.name, Table1.time, Table1.ampm, Table1.maindate, Table1.longdate, Table1.av, Table1.comment, Table1.ipadd, Table1.replies, Table1.replydate, Table1.replytime, Table1.replyampm, Table1.total, Table1.browser ORDER BY Table1.id DESC;"

Any ideas?

Many thanks in advance.

Richard

[bluegenel]

I haven't tested this code yet, but a variation on this using FIRST works. So, I replaced Table1.comment with FIRST(Table1.comment) AS FirstOfcomment and deleted Table1.comment from the GROUP BY.

strSQL = "SELECT COUNT(Table2.id_no) AS CountOfid_no, Table1.id, Table1.name, Table1.time, Table1.ampm, Table1.maindate, Table1.longdate, Table1.av, Table1.ipadd, Table1.replies, Table1.replydate, Table1.replytime, Table1.replyampm, Table1.total, Table1.browser , FIRST(Table1.comment) AS FirstOfcomment FROM Table1 LEFT JOIN Table2 ON Table1.id=Table2.id_no GROUP BY Table1.id, Table1.name, Table1.time, Table1.ampm, Table1.maindate, Table1.longdate, Table1.av, Table1.comment, Table1.ipadd, Table1.replies, Table1.replydate, Table1.replytime, Table1.replyampm, Table1.total, Table1.browser ORDER BY Table1.id DESC;"

And I then used Response.Write (rsDirector("FirstOfcomment"))

instead of

Response.Write (rsDirector("comment"))

Thanks.

Richard

[bluegenel]

The reason being, you can't use GROUP BY with a memo field in Access. This is a known problem as far as Microsoft are concerned. So they recommend using FIRST instead.

Richard