select query return all records if no match (1 Viewer)

hardyd44

Registered User.
Local time
Today, 18:33
Joined
Nov 22, 2005
Messages
77
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

Banishment Pending
Local time
Today, 13:33
Joined
May 11, 2011
Messages
11,611
Can you post the SQL of your query?
 

hardyd44

Registered User.
Local time
Today, 18:33
Joined
Nov 22, 2005
Messages
77
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

Have been here a while
Local time
Today, 19:33
Joined
Jun 17, 2012
Messages
7,732
...
- 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

Registered User.
Local time
Today, 18:33
Joined
Nov 22, 2005
Messages
77
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

Have been here a while
Local time
Today, 19:33
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good luck.
 

Users who are viewing this thread

Top Bottom