Changing criteria on the fly

[Guest]

I have a form that has a check box and a button that runs a query

Inf the field line of the query I have the field

Auctio

In the criteria section I have

IIf([Forms]![frmReports].[chkAuctionAll]=-1,Like "*",GetAuction()

When the box IS NOT CHECKED the query runs fine and brings in the criteria from GetAuction(). However when the box is checked no records are brought in when it should be bringing all records in.

One more caveat. When I save and close the query and then go back into it Access has changed the criteria line to

IIf([Forms]![frmReports].[chkAuctionAll]=-1,([tblSmartSourcingCostSavings].[Auction]) Like "*",GetAuction()

Any help would be appreciated. Additional things if have tried are

IIf([Forms]![frmReports].[chkAuctionAll]=-1,"",GetAuction()
IIf([Forms]![frmReports].[chkAuctionAll]=-1,,GetAuction()
IIf([Forms]![frmReports].[chkAuctionAll]=-1,Not is Null,GetAuction()

 

[John Spencer (MVP)]

Try the following. You can change the parameter of the criteria using an IIF
statement, but you cannot change the comparision operator. Your method was
attempting to change between equals and Like.

Like IIf([Forms]![frmReports].[chkAuctionAll]=-1,"*",GetAuction())

 

[Guest]

Thanks a lot John. It worked beautifully.