select query return all records if no match

[hardyd44]

I think I am having a mental block here,

I have a select query based on 1 table, and want to filter the data from 2 combo boxes on a form..

so good so far, pretty standard stuff, however both of the combo boxes will always have a value but the column in the table may not (it will be null), what I am after is after the user has selected a value in the combo box if there is no match - show all records, if there is a match show only matching records

I am using Forms!form!combobox as the parameters in the query

Does that make sense?

 

[plog]

Can you post the SQL of your query?

 

[hardyd44]

SQL:

SELECT Tbl_Quotes_Details.QuoteNo, Tbl_Quotes_Details.CreatedDate, Tbl_Quotes_Details.CustomerNo, Tbl_Quotes_Details.SiteNo, Tbl_Quotes_Details.SiteName, Tbl_Quotes_Details.EmailedTo, Tbl_Quotes_Details.TotalCost, Tbl_Quotes_Details.documentlink
FROM Tbl_Quotes_Details
WHERE (((Tbl_Quotes_Details.CustomerNo)=[Forms]![Frm_UOL_Job_Entry]![Customer]))
ORDER BY Tbl_Quotes_Details.CreatedDate DESC;

The field underlined & bold the second variable it is numeric and can be either null, matched or unmatched with the value in the combo box in the form.

if null or unmatched, then show all records, if matched then show only those records - does that make sense?

 

[JHB]

...
- does that make sense?

Sorry, not really, I'm not sure you're able to get that in one query.
What I would suggest is to first check whether it is data equal to the combo box.
If it does then run the query you have, if not then run the query without criteria.

 

[hardyd44]

JHB,

brilliant idea, I will run a separate dcount query first to check if any matches, if not then run the query without that parameter.

can do this in VBA, just need to work out the details, but like the principle

thanks for your help

Regards

Dean

 

[JHB]

You're welcome, good luck.