junk characters in query result

[mcnewsxp]

when i use a kind of large (150 characters or more) text field as the main
sort criteron in a query i get a single japanese or other unexpected
character in the text field instead of my text.
anybody else seen this and know what to do about?

tia,
mcnewsxp

 

[Guest]

That is strange. Please post the SQL for the query.

Also it could be a corruption issue. Tony Toews has an excellent web page on
database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[mcnewsxp]

i should have included that the text field is *grouped* on too.
if i add another field that has the same data in it for all records and make
that the top level grop it looks fine.
i don't have the sql handy right now tho so may have to wait till monday to
post.

 

[mcnewsxp]

SELECT tblIsolation.comments, Count(tblIsolation.comments) AS
CountOfcomments FROM tblData INNER JOIN (tblIsolation INNER JOIN
tblIsolationDetail ON tblIsolation.isolationnum =
tblIsolationDetail.isolationnum) ON tblData.DASH =
tblIsolationDetail.dashnum GROUP BY tblIsolation.comments

 

[Guest]

Nothing in the SQL looks strange except that I don't see a sorted / Order By
clause. Even then there's nothing that should cause such a problem. I think
it could well be a corruption problem. tblIsolation.comments is the large
text field I assume. You might want to add criteria to return a greatly
reduced number of records and see what happens then.

 

[Ken Snell \(MVP\)]

This is a common problem with Group By on a memo field. Assuming that you do
not actually need to group on that field in order to ensure uniqueness,
change the Group By aggregate to First.

 

[mcnewsxp]

Nothing in the SQL looks strange except that I don't see a sorted / Order

By
clause. Even then there's nothing that should cause such a problem. I
think
it could well be a corruption problem. tblIsolation.comments is the large
text field I assume. You might want to add criteria to return a greatly
reduced number of records and see what happens then.

you're right - it should have been grouped on and not ordered by.
again, the corrupted output goes away if i also group my for example STATE.
in this case all records have the same STATE.

 

[mcnewsxp]

This is a common problem with Group By on a memo field. Assuming that you

do not actually need to group on that field in order to ensure uniqueness,
change the Group By aggregate to First.

what do you mean change it to first? the first field in the grouping?
unfortuneately it must be grouped on the text value.
maybe i need to add a related table for the text values.

 

[Ken Snell \(MVP\)]

First is an aggregate function that can be used in Totals queries. If you
indeed must group on that text field, then my suggestion is not going to be
useful.

Sometimes, this situation can be alleviated if you turn off Unicode
compression for that field in the table's properties. (This assumes that you
don't need unicode character representation/storage for the field; if you're
using English characters only, you should be ok to turn it off.)