Query Criteria

[Sam]

I have a query where I have specified criteria as;

WHERE (((tblEnquiries.EnqName)=[Enter Cust Name]))

I want to add the condition that if nothing is entered in the Input Box, All records to be displayed.

How should I do that ?

[Welshgasman]

SELECT Employees.[First Name]
FROM Employees
WHERE (((Employees.[First Name])=[Enter name])) OR (([Enter name] Is Null));

[Sam]

That gives me the Input Box twice, and displays all records, because of the second condition

ETA - That works, thanks. The error was because I just copied your tbl and field names.

[Welshgasman]

That gives me the Input Box twice, and displays all records, because of the second condition

ETA - That works, thanks. The error was because I just copied your tbl and field names.

Never a good idea.

That is because I tested that query before posting, as I was not sure that was the syntax.

[Sam]

Never a good idea.

That is because I tested that query before posting, as I was not sure that was the syntax.

There is no icon for a knock on the head, but consider it done.

[Sam]

Further Question
If we want to avoid user error in typing the Names, can this be done with a drop down list, like a filter ?

[Minty]

You can use a combo box on a form with the names / ID's as the rowsource and use the same syntax in the query:

WHERE  tblEnquiries.EnqName = Forms![YourFormName].[cmbYourCombo] or Forms![YourFormName].[cmbYourCombo] is Null

As per last time replace with your form and control names.
In the query designer it should bring up the names for you once you type forms! in the criteria box.

[Sam]

I'm doing that but draw a blank each time.

WHERE tblEnquiries.EnqName = Forms![frmDropDown].[cbodropdown] or Forms![frmDropDown].[cbodropdown] is Null

Is it because after criteria selection I'm using a Run Query button. The button has this On Click

DoCmd.OpenQuery "qryBilling"
 Me.cbodropdown = ""

[Minty]

Can you post up a zipped copy of your database?
Only need some sample data and the forms you are having an issue with.

[Sam]

Sure. Here we go. But I have removed the DropDown form from this. Maybe you can suggest some other optimizations as well.
V 7.0 Trials.zip

[Sam]

This one has everything. But a WiP.

V 7.0 TrialsM.zip

[Minty]

Which form should I be looking at?

Edit: ignore me I worked it out

Your problem is cause by referring to the visual combo results not the bound column.
Your combo is storing the EnqID not their name so you need to use the criteria against that:

SELECT tblBilling.BillID, tblEnquiries.EnqName, qryBookingValues.WIng, qryBookingValues.UnitNo, tblBilling.BillNo, tblBilling.BillDate, tblBilling.BillPercent, tblBilling.BillDetails, qryBookingValues.ValAgr, qryBookingValues.ValGSTAgr, [ValAgr]*[BillPercent]/100 AS ValBillAgr, [ValGSTAgr]*[BillPercent]/100 AS ValBillGST, [ValBillAgr]+[ValBillGST] AS ValBillTotal
FROM tblEnquiries INNER JOIN ((tblBilling INNER JOIN tblBookings ON tblBilling.BookingID = tblBookings.BookingID) INNER JOIN qryBookingValues ON tblBookings.BookingID = qryBookingValues.BookingID) ON tblEnquiries.EnqID = tblBookings.EnqID
WHERE (((tblBookings.EnqID)=[Forms]![frmDropDown].[cbodropdown] Or [Forms]![frmDropDown].[cbodropdown] Is Null))
ORDER BY tblBilling.BillID;

[Sam]

Thanks Minty,
I have it working now.