MS Access query returning Chinese characters - possible table corruption?

Question

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

SELECT a, b from table1

everything is fine. If I instead do

SELECT a, b from table1 group by a, b

I get really weird characters as a result. At first I got upside down L's, but now I'm getting Chinese characters. It's weird because other queries in my database use the table and get the desired output. It seems like it's only when I do a group by that I have the problems. Any suggestions? I was ready to roll it out, but now I'm getting these errors!

Pistoleer

How are you connecting to and reading from your Access database? (What program are you running the queries in?). What collation are you using? – Dettmer

I'm constructing the queries in a subroutine in VBA. I then set a listbox rowsource equal to the query. Even if I just write a query in the query builder in Access, though, I see the same thing. I wasn't getting this before I made the update to the data. – Pistoleer

Please post a screenshot which includes the results you're seeing as well as your data in Table view. – Dettmer

Answer 1

This is a bug typically met if grouping on a 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] & "")

Overblouse

This is ridiculous, but good to know! Thank you so much. This fixes the issue. – Pistoleer

I used the first example that used the Left function. – Pistoleer

Select a, Left(b, 255) As b From table1 Group By a, Left(b, 255) – Pistoleer

Plus one for putting me on the right track with the "Memo Field" reference. – Biological

Answer 2

I have just had the same issue in various reports. The problem is indeed the Memo Field.

The solution that worked for me was more straight forward... I had to remove the "Group by" for the Memo field and the problem disapeared.

I realize this might not be an option in every situation, but if it is, this is the easiest solution as it requires no rewrite of the SQL or even any other change in the DB.

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

Biological

Answer 3

Here is yet another option, which I just tried successfully. I was updating a query that someone else had created, and the author had included every field in the Group By clause, to return distinct records. I removed the entire Group By clause and inserted DISTINCT right after SELECT. No more Chinese. This may not be possible in some situations, but in this case it was a simple fix.

Also, I would not have thought of this if not for the insights offered above. Thanks everyone!

Gibbon

Answer 4

After five months of no problems, I had this issue today on a group query which included a "Count" field, so the "DISTINCT" technique wouldn't work. What I did was wrap my LongText field around the offending field. In this table, all of the fields are ShortText except for "Description". So the field groups by CSTR([Description]) - and now it works fine! By the way - this came shortly after a MS Office 365 update!

Coff

Answer 5

I had the same issue, but I didn't have any memo field or group by statements in my query. Mine resulted with using a switch statement on a date column.

The following did NOT work:

Activity_Status_Date_Completed: Switch([Activity_Status]="Completed",[Activity_Status_Date],true,null)

And the following did work (replaced null at the end with ""):

Activity_Status_Date_Completed: Switch([Activity_Status]="Completed",[Activity_Status_Date],true,"")

Sillsby