iif statement in a query used for criteria (1 Viewer)

unfortunately you can't create your criteria like that it needs to be like this:

Show me your full query in code brackets please.

Thank you both for your help

My query in SQL View looks like this (I hope this is what you meant):


SELECT Main.PermitNumber, Main.PERM, Main.PermitType, Main.Status, Main.DateOfEnquiry, Main.DateFormsSent, Main.CompanyName, Main.CompanyBuildingNameOrNumber, Main.CompanyStreet, Main.CompanyTown, Main.CompanyCounty, Main.CompanyPostCode, Main.ContactName, Main.ContactNumber, Main.ContactEmail, Main.SiteNameOrNumber, Main.SiteStreet, Main.SiteTown, Main.SitePostCode, Main.DateFormsRecieved, Main.DateDepositPaid, Main.DateFeePaid, Main.DepositValue, Main.FeeValue, Main.DateInsuranceExpiry, Main.DateDepositReturned, Main.Vehicle1Registration, Main.Vehicle1Weight, Main.DatePermitApproved, Main.Scanned, Main.OverOrUnderOneAndHalf, Main.Comments, Main.Reason, Main.PhotosOfSiteAfterWorkComplete, Main.NNoticeReceived, Main.LevelAInspectionDateCompleted, Main.RNoticeDateCompleted, Main.LevelBInspectionDateCompleted, Main.GauranteeExpiryDate, Main.[LetterSentConfirmationCompletionOfWorks-Date], Main.[LetterSentConfirmingCompletionOfWorks-Date], Main.LevelCInspectionDueDate, Main.LevelCInspectionCompletedDate, Main.[LetterSentConfirmingGauranteePeriodHasExpired-Date], Main.Flag, Main.ActionPoints
FROM Main
WHERE (((Main.Status)=IIf([Forms]![SearchForm]![IncludeClosed]=-1,[Status],[Status]<>"Closed")) AND ((Main.CompanyName) Like "*" & [Forms]![SearchForm]![Cname] & "*") AND ((Main.CompanyBuildingNameOrNumber) Like "*" & [Forms]![SearchForm]![Cnum] & "*") AND ((Main.CompanyStreet) Like "*" & [Forms]![SearchForm]![Cstreet] & "*") AND ((Main.CompanyTown) Like "*" & [Forms]![SearchForm]![Ctown] & "*") AND ((Main.CompanyCounty) Like "*" & [Forms]![SearchForm]![Ccounty] & "*") AND ((Main.CompanyPostCode) Like "*" & [Forms]![Cpostcode] & "*"));


I tried taking out the text in red and replacing it with this blue code code CJ_London kindly posted:

WHERE ([Forms]![SearchForm]![IncludeClosed]=-1 AND Status="closed") OR ([Forms]![SearchForm]![IncludeClosed]=0 AND [Status]<>"Closed")

But when I did this and clicked save I was met with an error message saying "Extra ) in query expression"

The search part of my query works fine and when my tick box is ticked, it allows 'Closed Permits' to be included in the search, but when it is not ticked I get 0 results.

Thank you for your help so far, it is much appreciated.

You should try going over your brackets and letting me know how you are making out from there.

Show me your full query in code brackets please.

Click to expand...

(I hope this is what you meant):

Click to expand...

Almost - correct for the full query but not in code brackets - these can be found in the advanced editor on the # button

you didn't explain that there are other elements in the criteria. I suspect you need an extra two brackets just after the WHERE and one at the end

Alternatively remove all the other brackets - they are not required. The ones I provided are required to separate 'ands' and 'ors' to maintain the logic

CJ_London said:

Almost - correct for the full query but not in code brackets - these can be found in the advanced editor on the # button

you didn't explain that there are other elements in the criteria. I suspect you need an extra two brackets just after the WHERE and one at the end

Code:Copy to clipboard

WHERE ((([Forms]![SearchForm]![IncludeClosed]=-1 AND Status="closed") OR ([Forms]![SearchForm]![IncludeClosed]=0 AND [Status]<>"Closed"))

Alternatively remove all the other brackets - they are not required. The ones I provided are required to separate 'ands' and 'ors' to maintain the logic

Click to expand...

I need to learn to explain things like this.
The same things I have said, just 1000x better
Thanks thought