I have a query where I have specified criteria as;
I want to add the condition that if nothing is entered in the Input Box, All records to be displayed.Code:WHERE (((tblEnquiries.EnqName)=[Enter Cust Name]))
How should I do that ?
I have a query where I have specified criteria as;
I want to add the condition that if nothing is entered in the Input Box, All records to be displayed.Code:WHERE (((tblEnquiries.EnqName)=[Enter Cust Name]))
How should I do that ?
Code:SELECT Employees.[First Name] FROM Employees WHERE (((Employees.[First Name])=[Enter name])) OR (([Enter name] Is Null));
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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 ?
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:
As per last time replace with your form and control names.Code:WHERE tblEnquiries.EnqName = Forms![YourFormName].[cmbYourCombo] or Forms![YourFormName].[cmbYourCombo] is Null
In the query designer it should bring up the names for you once you type forms! in the criteria box.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
I'm doing that but draw a blank each time.
Is it because after criteria selection I'm using a Run Query button. The button has this On ClickCode:WHERE tblEnquiries.EnqName = Forms![frmDropDown].[cbodropdown] or Forms![frmDropDown].[cbodropdown] is Null
Code:DoCmd.OpenQuery "qryBilling" Me.cbodropdown = ""
Can you post up a zipped copy of your database?
Only need some sample data and the forms you are having an issue with.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
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
This one has everything. But a WiP.
V 7.0 TrialsM.zip
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:
Code: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;
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Thanks Minty,
I have it working now.