IIF Statement in a query criteria

[Guest]

I have a form where users input various query criteria. I have one field in particluar wher a user might want to search a field for a certain text string that they have entered in a text box. However, I also want to offer the user the inverse option i.e. to return records where the field doesn't contain the search text. I wanted to use a check box on the form for users to indicate what their preference is i.e. matching records or not matching records

I then tried to use the following expression in the criteria of the search field ([tblProperties].[county]) in the query:

IIF([Forms]![frmAvailability]![chkNotLikeCounty]=Yes,
[tblProperties].[county] Not Like "**" & [Forms]![frmAvailability]![txtCounty] & "**",
[tblProperties].[county] Like "**" & [Forms]![frmAvailability]![txtCounty] & "**")

However, I'm getting an error message. Can anybody suggest why?

 

[Nikos Yannacopoulos]

Been there, I know what you mean!

I think the problem is that whatever the Iif statement returns is treated as text string, so operators and references are not recognized as such.

Unless someone comes up with a better idea, I would suggest you use some code to apply filter(s) based on the criteria form controls.