Version upgrade problem -- Null value in combo box rowsource

[John Morrelles]

I've used UNION queries in combo row sources for years to provide a
"None" or "All" choice at the top of the combo box list like this:

SELECT "<All>", Null FROM tblStatus UNION SELECT StatusDescription,
StatusID FROM tblStatus ORDER BY StatusDescription

As users have moved from Access 97 to Access 2002 and 2003, my
applications are breaking because the result set for the combo box is
corrupted. The column where I specify the Null for the first row
contains corrupted characters, like wingdings, rather than the numeric
ID values that should be there.

Has anybody else experienced something like this? I'm having to go
through my combo boxes and change the Null to an empty string ("") and
rewrite my code to look for empty strings rather than checking for
IsNull.

 

[Chadlon]

John,

Perhaps it is the case that the query you are actually running differs from
that posted?

It seems to me that the query as posted would deliver a syntax error, in
that the order statement refers to a field not defined for the
first-mentioned recordset.

Normally, I would use something like:
SELECT DISTINCT "<All>" As StatusDescription, Null As StatusId FROM
tblStatus
UNION SELECT StatusDescription, StatusId FROM tblStatus
ORDER BY StatusDescription

Never had any trouble.

Some would suggest the use of a guaranteed-to-deliver-only-one-record table
for the first select clause, but if tblStatus is not too big, probably won't
hurt as it is.

 

[John Morrelles]

You are correct about the column label. I use it and was being lazy
when I typed the SQL here.

Here's an actual SQL statement that generates this error:

SELECT "<None>" AS status_desc, Null AS status_id FROM tblJobStatus
UNION
SELECT tblJobStatus.status_desc, tblJobStatus.status_id
FROM tblJobStatus;

tblJobStatus is defined as:
status_id, AutoNumber
status_desc, Text

I played around with it some. Adding the DISTINCT qualifier to the
first half of the statement solves the problem.

Thanks,
John