Problem adding (All) to numeric field combo box

[gmazza via AccessMonster.com]

Hey there,
I am having a problem with my combo box. I have 3 in total and 2 of them are
Text fields so when I add the (All) to my Union query everything is fine. I
figured out a way to add (All) to my numeric field combo box but my
AfterUpdate code on this ssame combo box keeps erroring.
Here is my Row Source for my combo box:
SELECT DISTINCT dbo_DDXLMO.HoleSz, dbo_DDXLMO.HoleSz FROM dbo_DDXLMO UNION
SELECT Null, '(All)' As HoleSz FROM dbo_DDXLMO;
Column Count: 2
Column Widths: 0;2
Bound Column: 1

Once I select (All) on my form, I get a syntax error in my AfterUpdate
event for the HoleSz combo box. Its a syntax error (missing operator) in
query expression and HoleSz =
For my Text box combo boxes that work when I choose (All), in the query
expression it actually has a "
For example, the same query expression where I'm getting an error it says and
BitType = " and HoleSz =

If I take out BitType so I just want to see where its erroring exactly, it
gives the same error on HoleSz as I'm guessing it doesn't like HoleSz =
nothing.

How can I make this work?
Thanks in advance.

 

[Klatuu]

There is an issue with data types. I see you are repeating dbo_DDXLMO.HoleSz
to get the All to display, bu I suspect dbo_DDXLMO.HoleSz is a numeric value.
The data types for each field on both sides of the Union have to be the
same. If you are wanting to use the HoleSz in one column to do the lookup
and display the hole size in the other, you need to cast the second occurance
as text and give it a different name so you can use the (All) text in that
same column. The other issue is the values in HoleSz. I will assume it is a
long integer and none will have the value of 0. If so, you will need to use
a negative number. A null will not work correctly here because of the type
casting. Try this:

SELECT DISTINCT HoleSz, Cstr(HoleSz) AS HoleSize FROM dbo_DDXLMO ORDER BY
HoleSz UNION SELECT 0 As HoleSz, '(All)' As HoleSize FROM dbo_DDXLMO;