Iff statements in 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 fiedd 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?

 

[John Vinson]

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 fiedd 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?

It's because you can only pass actual field values in a query
criterion; you can't pass operators such as LIKE or NOT LIKE.

Try a criterion of

WHERE ([County] LIKE "*" & [Forms]![frmAvailability]![txtCounty] & "*"
AND [Forms]![frmAvailability]![chkNotLikeCounty] = False) OR
([County] NOT LIKE "*" & [Forms]![frmAvailability]![txtCounty] & "*"
AND [Forms]![frmAvailability]![chkNotLikeCounty] = True)

to get around the restriction.

 

[Guest]

Thanks - worked a treat