Yes/No or Both - Form Filling Query Criteria 1

[Donamese]

I have a form that is feeding query criteria. There are 2 selections but I need a both as well. They are on the form in a combo box.

Ex. Gender - Male or Female
Terminated - Y or N

I tried setting the default value of the combo box as [Query]![Field]In("Male","Female") but it came back with no records. When I put a specific in everything runs fine.

Is there a means of providing a "Both" in the combo box or setting a default value where if the box is blank then it pulls both? Or would a list box where I could highlight both be a means of making it work?

 

[PHV]

What is the actual WHERE clause in the SQL code of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Donamese]

WHERE (((MKTGLIB_LEADGENRTR.EESEXC)=[Forms]![Lead]![Gender]) AND ((MKTGLIB_LEADGENRTR.AGE) Between [Forms]![Lead]![AgeL] And [Forms]![Lead]![AgeR]) AND (([Agent Info].[Agent ID])=[Forms]![Lead]![Agent]) AND ((MKTGLIB_LEADGENRTR.TERM)=[Forms]![Lead]![Term]));

 

[PHV]

You may try this:
WHERE (MKTGLIB_LEADGENRTR.EESEXC=[Forms]![Lead]![Gender] OR [Forms]![Lead]![Gender] IS NULL)
AND MKTGLIB_LEADGENRTR.AGE Between [Forms]![Lead]![AgeL] And [Forms]![Lead]![AgeR]
AND [Agent Info].[Agent ID]=[Forms]![Lead]![Agent]
AND (MKTGLIB_LEADGENRTR.TERM=[Forms]![Lead]![Term] OR [Forms]![Lead]![Term] IS NULL)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PWise]

MKTGLIB_LEADGENRTR.EESEXC=iif ([Forms]![Lead]![Gender]<>"both",[Forms]![Lead]![Gender],MKTGLIB_LEADGENRTR.EESEXC)

 

[Donamese]

Perfect....thank you!!!

 

[Donamese]

When I run the query using the code from PHV, it populates a new column and doubles up the data in the query criteria.

Initially it was entered as:

Field: EESEXC
Table: MKTGLIB
Sort:
Criteria: [Forms]![Lead]![Gender] OR [Forms]![Lead]![Gender] IS NULL


I use a select query option for the query. After the form runs the query the query looks like :

Field: EESEXC
Table: MKTGLIB
Sort:
Criteria: [Forms]![Lead]![Gender]
Or: [Forms]![Lead]![Gender]

Field: [Forms]![Lead]![Gender]
Table:
Sort:
Criteria:
Or:

Is Null
Is Null


Not quite sure why the query modifies itself while running a form.

 

[PHV]

Unfortunatly you're a victim of the query grid wizard doing silly stuff as soon as you want to play with the OR operator ...
I personally never ever use this buggy tool and write directly in the SQL view pane.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Donamese]

Can I just click Query -> Select Query and save? Or does it not transfer to the new style clean?

 

[Donamese]

Sorry...Query->SQL Specific->Pass Through was the one I was wondering.