Asian characters in query - asian.jpg (0/1)

[Kelvin Wilson]

I have a query that is producing Asian (Chinese? Japanese?) characters
where there should be a null. It's a memo field for future use.

Can anyone tell me what could possibly be causing this?


Kelvin

 

[Dirk Goldgar]

I have a query that is producing Asian (Chinese? Japanese?) characters
where there should be a null. It's a memo field for future use.

Can anyone tell me what could possibly be causing this?

This is probably due to some sort of corruption in your database. Memo
fields are particularly susceptible to this, because the memo data is
stored on a separate page from the rest of a record's data. See if the
various suggestions on Tony Toews' Currupt MDBs FAQ page help:

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Kelvin Wilson]

This is probably due to some sort of corruption in your database. Memo
fields are particularly susceptible to this, because the memo data is
stored on a separate page from the rest of a record's data. See if the
various suggestions on Tony Toews' Currupt MDBs FAQ page help:

http://www.granite.ab.ca/access/corruptmdbs.htm

Thanks for your help, Dirk (and Tony by Proxy). But It's still there.

This is what I did.

1. Backed up existing data. (It's a Back End)

2. Made a new blank db

3. Imported all tables into the new blank db

4. Opened Front End. Characters still there in query and report, not
in linked table. Closed Front End.

5. Removed Memo field from table in Back End. (No data in the fields
at all.)

6. Inserted new text field with the same name in table in Back End.

7. Opened Front End. No characters (of course). Closed Front End.

8. Changed data type in table in Back End to Memo.

9. Opened Front End. Characters back.

Weird, huh?


Kelvin

 

[Kelvin Wilson]

Further investigation reveals that it's the query that is producing
the characters. It's happening when I press the "Totals" button (the
Sigma symbol) to sum the values the "Group By" on the Memo field seems
to be the culprit.

 

[John Vinson]

the "Group By" on the Memo field seems
to be the culprit.

That's because you can't Group By a memo field!

Think about it: a memo field could potentially contain a gigabyte of
data. The program would need to compare two gigabyte long strings to
see if they're identical. NOT fun...

Use the First function to retrieve one record's memo, or just leave
memo fields out of totals queries altogether.

 

[Dirk Goldgar]

Further investigation reveals that it's the query that is producing
the characters. It's happening when I press the "Totals" button (the
Sigma symbol) to sum the values the "Group By" on the Memo field seems
to be the culprit.

Grouping by a memo field can be problematic. IIRC, some versions of Jet
(the database engine used by Access) can't do it at all, while some can
do it but only group on the first 256 characters of the field. We could
look into exactly what Access and Jet version you have installed, but
most likely you can just avoid grouping on the field at all. As John
Vinson said in his reply, you can probably use the First() aggregate
function in the query to pull the relevant memo data instead of grouping
on it.

 

[Kelvin Wilson]

On Wed, 11 Feb 2004 13:06:07 -0500, "Dirk Goldgar"


The First() function will do nicely, thankyou.

Thanks for the help.