Combo box controlled query 2

[Trees]

sorry for the repeating of a previous thread but i have looked at all of the replys on the first combo box controlled query and have followed what was reccomended but i still have some problems.


i have three combo boxes and i want to query a single table with the selected combo options.

atm i am using the criteria part of a query to filter the results by the combo box
[forms]![Form1]![cboStaff]

this works well for one thing but when i want two i have put them in line which means AND so both combo boxes have to have a value in and then the query looks for combo box ones value with combobox 2's value.

is there any way i can make the value in the second combo box mean any result. which would effectivly turn the query into a search for just the first criteria set by the combo box???

please help i have been going round in circles trying to figure out how i am going to do this

 

[directormac]

Nz - Your Friend

Not entirely sure I understand your question, but it sounds like you want to have a query that includes criteria for multiple fields (defined via multiple controls) which allows the user to leave any number of the controls empty without breaking the query. Yes?

You need to use an Nz statement in each of your criteria lines to check if the control is empty and use "*" if that's the case. Something like:

=Nz(Forms![frmMyForm]![cboMyComboBox],"*")

Check Access help for info on Nz. You may also find this information useful as background. It explains the difference between Null, Zero, and Empty values.


--IsNotNull Mac

 

[Trees]

Am i using this the right way ?

i have tried to use the Nz statement but it seems to do the same thing with the combo boxes. this is the SQL view of the query

SELECT enquiry.[Taken By], enquiry.Client, enquiry.Status, enquiry.[Enquiry Ref]
FROM enquiry
WHERE (((enquiry.[Taken By])=Nz([forms]![Form1]![cboStaff],"*")) AND ((enquiry.Status)=Nz([forms]![Form1]![cboStatus],"*")))

Do i need to remove the AND's ?? . What happens when i run this if one of the combo boxes has nothing selected it will query for the firts combo box entry and "" (noting in the other). im trying to get it to ignore the nothing in the second combo box .

Has anyone got any ideas on how i would do this ??

 

[directormac]

Did you read this thread?

--Curious Mac

 

[Trees]

no i didn't read that thread. my searches of the site didn't throw it up. Thanks for the pointer though it seems to be spot on and should solve all my problems.
CHEERS mate