Adding a NULL value

[Guest]

I have severeal queries to return unique values in different fields. I'm
using the queries as inputs for different combo boxes on a form. the form
then generates another query to give data to a report. the onlly problem is
the queries to give the unique values always return a value. i'm looking for
a way to add a NULL value to the query return so the user can "deselect" a
combo box once that field is no longere needed.

any suggestions?

 

[Guest]

Istead of adding a value to the combo, and let the user to look for the Null
value, create a button next to the combo that will remove the value from the
combo

With the code on the OnClick event

Me.ComboName = Null

 

[Allen Browne]

Could the user just delete the value in the combo, so it is Null?

Could you include that instruction in the StatusBarText property of the
combo to help them out?

If the combo is unbound and you want to proceed anyway, you could create
another table with just one record to show the "(Blank)" or whatever text
you want, and then create a UNION query on that table and your current one.
The UNION query would become the RowSource of the combo.

This article shows how to add "All" to the combo. You could do the same
thing, but just change the text to what you want:
http://www.mvps.org/access/forms/frm0043.htm

 

[Guest]

I'll try that, but I'd still like to have the null value in the drop down menu.

 

[Allen Browne]

Ouch!

I'm sure that Ofer Cohen knows that line won't work.

Use IsNull() to test if the value is null, i.e.:
If IsNull(Me.ComboName) Then ...

 

[Guest]

Hi Allen,
This line of code that I provided wasn't ment for a "If" criteria, it was
simply used to set the combo to Null, instead of adding a Null value to the
combo, this is why I didn't use the IsNull

 

[Allen Browne]

Ah, sorry: I misunderstood you.

Your Access knowledge is good, so I was surprised.
Now I understand it was an assignment, not a comparison.

 

[Guest]

The above code worked fine. Just add lines for each combo box. Not too bad.
I'd still like to try to add a null value for the query. i found some other
stuff.

I have another question regarding printing, but that'll be in another post.

Thanks for all your help!!!

 

[Guest]

Ok, I finally got it. That article you sent Allen was hard to follow. i
ended up stumbling upon this article
http://support.microsoft.com/?kbid=208707 that did exactly what i wanted. i
was havinga problem because i'm only using one field, and they used two.

so i have one select distinct field, but two union select null, null in the
command, but only need one (it gave me an error that the number of columns
didn't line up, i removed one null and it worked fine.)