Foreign charset showing up unexpectedly

[Gary K]

I'm getting some very strange results from a query I'm running. I've boiled
it down to a problem when using a Group By query when joining tables on two
or more columns. In that case, one of the fields is showing some Asian
character set (Korean, I think) in the result. In viewing the table
directly, or querying with only one joined column or no Group By, the normal
English values show.

Here is the query that returns Korean in the Description field (note: this
is a scaled-down query just to demo the problem):
SELECT Tasks.Description, Max(Tasks.InitialLOE) AS MaxOfInitialLOE
FROM Tasks INNER JOIN [Time] ON (Tasks.Project = Time.[Dev Project]) AND
(Tasks.SummaryTask = Time.[Dev Summary Task])
GROUP BY Tasks.Description;

This query works fine:
SELECT Tasks.Description, Max(Tasks.InitialLOE) AS MaxOfInitialLOE
FROM Tasks INNER JOIN [Time] ON Tasks.Project = Time.[Dev Project]
GROUP BY Tasks.Description;

As does this one:
SELECT Tasks.Description, Tasks.InitialLOE
FROM Tasks INNER JOIN [Time] ON (Tasks.SummaryTask = Time.[Dev Summary
Task]) AND (Tasks.Project = Time.[Dev Project]);


Any suggestions are most appreciated!
Thanks,
Gary

 

[Gary K]

One more thing - Description is a Memo field.

 

[John W. Vinson]

I'm getting some very strange results from a query I'm running. I've boiled
it down to a problem when using a Group By query when joining tables on two
or more columns. In that case, one of the fields is showing some Asian
character set (Korean, I think) in the result. In viewing the table
directly, or querying with only one joined column or no Group By, the normal
English values show.

Memo fields changing to Korean or other languages is an early symptom of
corruption. See
http://www.granite.ab.ca/access/corruptmdbs.htm
for a discussion of symptoms and cures. MAKE SURE YOU HAVE a backup (hopefully
from before you saw this problem, if not make one *NOW*); try compacting the
database to see if that fixes the problem.

 

[John Spencer]

Memo fields are not handled well in aggregate queries. The problem is the
size of a memo field can be up to 32K (or larger if you use code to fill the
field).

IF you can get by with just the first 255 characters of the field you can
***TRY*** rewriting your query and see if it works.

SELECT Left(Tasks.Description,255)
, Max(Tasks.InitialLOE) AS MaxOfInitialLOE
FROM Tasks INNER JOIN [Time]
ON (Tasks.Project = Time.[Dev Project]) AND
(Tasks.SummaryTask = Time.[Dev Summary Task])
GROUP BY Left(Tasks.Description,255);

You could even do the above and get the first 510 characters using two fields
that you can recombine when you display the data in a report or on a form.

SELECT Left(Tasks.Description,255) as Part1
, MID(Tasks.Description,256,255) as Part2
, Max(Tasks.InitialLOE) AS MaxOfInitialLOE
FROM Tasks INNER JOIN [Time]
ON (Tasks.Project = Time.[Dev Project]) AND
(Tasks.SummaryTask = Time.[Dev Summary Task])
GROUP BY Left(Tasks.Description,255)
, MID(Tasks.Description,256,255)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Gary K]

Thanks for the response. A link in the website you referenced
(http://allenbrowne.com/ser-47.html) states:
If the strange characters appear only in the query, not when you view the
table, this is not a corruption. This occurs when JET is unable to determine
the data type of the query field, and is triggered by lots of situations.

This is the case in my situation, so I am believing that it is not a
corruption. None of the data in that field is very big, so I converted it to
a Text field and that solved the problem.

The odd part of this is that joining on a second column caused the problem
to appear. I'm guessing that this is a bonafide bug in Access.

Thanks to all for your responses. Much appreciated!
Gary

 

[John W. Vinson]

If the strange characters appear only in the query, not when you view the
table, this is not a corruption. This occurs when JET is unable to determine
the data type of the query field, and is triggered by lots of situations.

This is the case in my situation, so I am believing that it is not a
corruption. None of the data in that field is very big, so I converted it to
a Text field and that solved the problem.

The odd part of this is that joining on a second column caused the problem
to appear. I'm guessing that this is a bonafide bug in Access.

Thanks for getting back. I hadn't run into that particular circumstance, so
it's good to know; and I agree, it's very much a bug!