voiding a query criterion

[papa jonah]

I have a form that allows a user to use dropdowns to select items to
use in a query.
It works good. However, I want to add another field for them to select
from. However, if they choose field Group, the Division dropdown
clears and vice versa. As long as each dropdown is used by seperate
queries, it works fine. What I would like to do is use the same query
such that me.group is the criteria in one field and me.division is the
criteria in another field.
In the afterupdate events for each of the comboboxes, I have code to
set the other to null.
But then my query doesn't return anything.
What should I use in place of null so that the combobox with a
selection will be able to work?
I hope this all made sense.

TIA

 

[John Vinson]

What should I use in place of null so that the combobox with a
selection will be able to work?

Your Forms with the Null combos will work, if you use criteria like

=[Forms]![YourForm]![cboDivision] OR [Forms]![YourForm]![cboDivision]
IS NULL


John W. Vinson[MVP]

 

[papa jonah]

I must not be applying this correctly because it is not working. Maybe
I am not placing this at the correct place. I have added this to the
afterupdate event for each of the dropdowns.
The result though is the query does not return any records. My guess
is it is using "null" as one of the criteria - and of course none of
the records have a null.

 

[John Vinson]

I must not be applying this correctly because it is not working. Maybe
I am not placing this at the correct place. I have added this to the
afterupdate event for each of the dropdowns.
The result though is the query does not return any records. My guess
is it is using "null" as one of the criteria - and of course none of
the records have a null.

Please post your actual code. I'm not sure just what you're doing!

My suggestion had NOTHING TO DO with code. I was suggesting that you
use a criterion in the Query.

John W. Vinson[MVP]

 

[papa jonah]

Here is the after update stuff for both comboboxes which are on the
same form. I only want the user to query based on one or the other.
If the user selects one, then tries to select another, I want it to
clear the first selection. But regardless of which one is left, I want
the query to be able to perform. Null apparently gets in the way
because null as a criterion (when none of the records are null) removes
all records from consideration.

Private Sub cboAD_AfterUpdate()
[Forms]![query form]![cboDivision] = Null
End Sub

Private Sub cboDivision_AfterUpdate()
Me.cboAD = Null
End Sub

The query:
SELECT [Data].Discovery_Date, QryDivision.Division,
[Data].[Designator], QryDivision.AD
FROM [Zlookup: Type] INNER JOIN ([Zlookup: Subtype] INNER JOIN (([Data]
INNER JOIN QryDivision ON [Data].[Designator] =
QryDivision.[Designator]) INNER JOIN TblTypeSubType ON
[Data].[Designator] = TblTypeSubType.[Designator]) ON [Zlookup:
Subtype].SubtypeID = TblTypeSubType.idSubtype) ON [Zlookup:
Type].TypeID = TblTypeSubType.Type
WHERE (((QryDivision.Division)=[Forms]![query form]![cbodivision]) AND
((QryDivision.AD)=[Forms]![query form]![cboAD]))
ORDER BY [Data].Discovery_Date;

 

[John Vinson]

Here is the after update stuff for both comboboxes which are on the
same form. I only want the user to query based on one or the other.
If the user selects one, then tries to select another, I want it to
clear the first selection. But regardless of which one is left, I want
the query to be able to perform. Null apparently gets in the way
because null as a criterion (when none of the records are null) removes
all records from consideration.

As I suggested earlier in the thread, change

WHERE (((QryDivision.Division)=[Forms]![query form]![cbodivision]) AND
((QryDivision.AD)=[Forms]![query form]![cboAD]))


to

WHERE (((QryDivision.Division)=[Forms]![query form]![cbodivision]
OR [Forms]![query form]![cbodivision] IS NULL) AND
((QryDivision.AD)=[Forms]![query form]![cboAD])
OR [Forms]![query form]![cboAD] IS NULL)


John W. Vinson[MVP]

 

[papa jonah]

That got me to the right point. Thanks!