Setting Null values in a UNION query in Access 2002

[Snowdie]

I'm trying to write what should be a very simple sql
UNION Query in order to drive a combo box on a form that
feeds a report.

I want to include a NULL record at the top of the query,
so that that the report's query can return ALL the
records if the user leaves the combo box unselected. I've
done this a hundred times in Access 97, but now I'm
working in Access 2002 and running into problems.

The query is like so:

SELECT Null AS ColorPK, Null AS Mycolor FROM color
UNION
SELECT color.ColorPK, color.Mycolor FROM color;

ColorPK is an autonumber field (although I get the same
errant results with a regular number field). Mycolor is a
text field.

In Access97, no problem; it returns like so (***
represents blank value in this message only):

ColorPK Mycolor
*** ***
1 pink
2 blue
3 indigo
4 black

However in Access 2002, instead of returning proper
numbers for the ColorPK field, it returns what appears to
be the ASCII lowercase character set!?! Along with the
null symbol (a little box) for any number of records
beyond the set.

Here's a sample of those results.

ColorPK Mycolor
*** ***
; red
' pink
/ blue
, indigo
{ black
 teal
 crimson


Has anyone ever encountered this? I've tried it on two
different machines with two separate OSs, so I don't
believe it is related to any system problems.

 

[Van T. Dinh]

Try:

http://www.mvps.org/access/forms/frm0043.htm

--
HTH
Van T. Dinh
MVP (Access)



I'm trying to write what should be a very simple sql
UNION Query in order to drive a combo box on a form that
feeds a report.

I want to include a NULL record at the top of the query,
so that that the report's query can return ALL the
records if the user leaves the combo box unselected. I've
done this a hundred times in Access 97, but now I'm
working in Access 2002 and running into problems.

The query is like so:

SELECT Null AS ColorPK, Null AS Mycolor FROM color
UNION
SELECT color.ColorPK, color.Mycolor FROM color;

ColorPK is an autonumber field (although I get the same
errant results with a regular number field). Mycolor is a
text field.

In Access97, no problem; it returns like so (***
represents blank value in this message only):

ColorPK Mycolor
*** ***
1 pink
2 blue
3 indigo
4 black

However in Access 2002, instead of returning proper
numbers for the ColorPK field, it returns what appears to
be the ASCII lowercase character set!?! Along with the
null symbol (a little box) for any number of records
beyond the set.

Here's a sample of those results.

ColorPK Mycolor
*** ***
; red
' pink
/ blue
, indigo
{ black
 teal
 crimson


Has anyone ever encountered this? I've tried it on two
different machines with two separate OSs, so I don't
believe it is related to any system problems.