Help Using "Is Not Null" in a form

[RonMcNatt]

I'm using the value from a form to populate the selection criteria for a query. When the form returns values like "Central" or "East" there is no problem.

But I also have an 'All' option which returns the value "Is Not Null". This statement works when you manually type it into the selction criteria for the query. But when it pulls this statement from the form, it treats it as a string and obviously finds no matches. I can't find a function to have this value treated as a literal statment when its returned from the form.

Any thought or alternative solution paths would be greatly appreciated.

Ron McNatt

 

[DreamerZ]

What about using the code to say something like:

If combobox.value="ALL" then query selection criteria="IsNotNull"
else combobox.selection=query selection criteria

Hope that helps.

DreamerZ

 

[vb6novice]

Ron,

Had something similar a few years back. I never could get it to give me records when All was the choice. It used a combo box for the choices and the Row Source Type was Value List and the Row Source was "All";"Central";"East";"North";"South".

I added a text box that would hold the result of the combo box selection, and the text box control source was:

=IIf([Forms]![frmSelector]![cboSelectARegion]="ALL","'Central' Or 'East' Or 'North' Or 'South'",[Forms]![frmSelector]![cboSelectARegion])

The text box was then used as the criteria for the query. I don't know how to make it work on for a combo box that has a table or query as the row source with using code, but if you have a few set values to choose from, it will do the job.

 

[RonMcNatt]

Thanks for the suggestions, but neither solution will work. Using the text box still seems to treat the value as a literal string when placing the information into the query. The first suggestion still uses "Is Not Null" as a string.

 

[cmmrfrds]

Instead of trying to handle the null situation in the criteria, handle it on the criteria field. That way you can put something like "ALL" or whatever text suits your fancy in the combobox.

NOT THIS
criteriafield = "Central"
criteriafield = "IS Not Null"

CHANGE TO

nz(criteriafield,"ALL) = "Central"
nz(criteriafield,"ALL) = "ALL"

If the criteria field is null it will be changed to ALL otherwise it remains what it is.