I am having problems with a query that populates a subform. I have a checkbox that when checked, all records should show up, and when unchecked shows all records without a date in the [Completed] field.

My code that I have been working with.

SELECT MainTbl.EnterDate, MainTbl.DateDue, MainTbl.Completed, MainTbl.WONumber, MainTbl.Customer, MainTbl.JobDesc, MainTbl.Employee, MainTbl.Approval, MainTbl.Deposit, MainTbl.Billed, MainTbl.CustID, MainTbl.Canceled
FROM MainTbl
WHERE (((MainTbl.Completed) Like IIf([Forms]![FrmMainScreen]![MainTbl subform].[Form]![ShowAll]=True,"*","IsNull")))
ORDER BY MainTbl.WONumber DESC;

Any suggestions?

Try:


I think instead of True / False, you can also use:

Thanks for the help. Since I am a little fuzzy on SQL Syntax, it took a little to understand how your example worked. But thanks to you, I understand it a bit more. I think I was asking the oppisite of what I was looking for, so this is what worked for me.

SELECT MainTbl.EnterDate, MainTbl.DateDue, MainTbl.Completed, MainTbl.WONumber, MainTbl.Customer, MainTbl.JobDesc, MainTbl.Employee, MainTbl.Approval, MainTbl.Deposit, MainTbl.Billed, MainTbl.CustID, MainTbl.Canceled
FROM MainTbl
WHERE ( [Forms]![FrmMainScreen]![MainTbl subform].[Form]![ShowAll]=True )
OR (( [Forms]![FrmMainScreen]![MainTbl subform].[Form]![ShowAll]=False )AND( MainTbl.Completed Is Null )AND (MainTbl.Canceled=False))
ORDER BY MainTbl.WONumber DESC;

I also had to add a Requery on the last line of the forms OnLoad section, otherwise, the initial opening of the form returned no records.

How many AND and OR can a query work with?

Thanks for the help and quick response!

You're welcome ... Glad you worked it out ...

I am not sure how you used the SQL String in your set-up but if adding a Requery works, then it should be fine.

Check Access Help topic "Access Specifications" for all sort of limits on Access, Access Queries, etc ...