odd characters and symbols in one column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a front end with tables linked to three back ends. In the front end
are a number of select queries and several union queries. One union query
based on two select queries generate 19 columns of data. In one column
called "lengthID", foreign characters and symbols are displayed, rather than
numbers. This column is an AutoNumber data type in one of the base tables
linked into the front end. Opening the linked tables or select queries do
not display these odd characters, but regular numbers as they should.

A number of possible solutions to the problem such as compacting and
repairing the front and back ends, recreating the query, creating a new front
end, etc. did not resolve the problem. This is repeated when generating an
Access report or pasted into Excel. I also tried this on another computer on
which the problem is repeated.

Is there a program fix for this? Is there something else I can try? Or,
could this be a software bug?

I would appreciate input.

Cheers,
Suzan
 
Susan, this can happen if Access misunderstands the data type.

You can demonstrate the problem with this query:

In this example, Access looks at the Null in the first query, and cannot
tell if the data type is number, so it defaults to Text. Then when it
performs the union, the binary numeric data is treated as text values, so
you have a text representation of the numeric value, or "odd characters and
symbols" as you describe it.

The solution is to swap the SELECT statements around so that the first one
gives Access the correct cues about the data type:
SELECT ID, MyNumber FROM Table2
UNION ALL
SELECT ID, Null As MyNumber FROM Table1;

If that is not practical, you can also fool Access into recognising the data
type with this circumlocution:
SELECT ID, IIf(False, 1, Null) As MyNumber FROM Table1
UNION ALL
SELECT ID, MyNumber FROM Table2;
Of course, the False part is never true, so the 1 is never returned, but
it's enough to give Access the idea that this is a numeric field.
 
Allen,

Thank you very much for solving this problem. Your first example is exactly
like the union query I had -- a null for the length ID column in the first
SELECT statement, then an AutoNumber in the second statement. Switching the
two statements, like you suggested, resolved this issue. I am amazed.

Thanks,
Suzan
 
Back
Top