Geek Answers Handbook

MS Access query returning Chinese characters - possible table corruption?

I copied and pasted a new version of the data into my MS Access table, and now I get weird characters in my queries. Essentially, if I say:

SELECT a, b from table1

everything is good. If I do instead

 SELECT a, b from table1 group by a, b

As a result, I get really strange characters. At first I turned L's upside down, but now I get Chinese characters. This is strange because other queries in my database use the table and get the desired result. It seems to me that only when I make a group, I have problems. Any suggestions? I was ready to roll it out, but now I get these errors!

+7
ms-access
source share
3 answers

This is a mistake commonly encountered when grouping in the memo field.

There may be several workarounds depending on your needs:

 Select a, Left(b, 255) As b From table1 Group By a, Left(b, 255) Select a, Mid(b, 1) As b From table1 Group By a, Mid(b, 1) Select a, First(b) As firstb From table1 Group By a Select a, DLookUp("b","table1","Id = " & [table1]![Id] & "") AS b From table1 Group By a, DLookUp("b","table1","Id = " & [table1]![Id] & "")
+10
source share

I just had the same problem in different reports. The problem is really in Memo Field.

The solution that worked for me was simpler ... I had to remove the "Group by" for the "Memo" field, and the problem disappeared.

I understand that this may not be an option in every situation, but if so, this is the simplest solution, since it does not require rewriting SQL or any other changes to the database.

I found this solution here: Allen Brown - Grouping by Memo field leads to garbage

+1
source share

Here is another option that I just tried successfully. I updated a query created by someone else, and the author included each field in the "Group by" sentence to return individual entries. I removed the entire Group By clause and inserted DISTINCT immediately after SELECT. No more Chinese. This may not be possible in some situations, but in this case it was a simple solution.

In addition, I would not have thought about this, if not for the ideas proposed above. Thanks everyone!

0
source share

More articles:


All Articles