Query IIf statement help

[mike760534211]

I am running a query where a criteria field is populated from a form. I can get it to work correctly if i choose something in the form field but i want it to return all records if no criteria is selected. I am using the following as my IIf formula.

IIf(IsNull([Forms]![Custom_Reports]![txtClient])," ",Like [Forms]![Custom_Reports]![txtClient] & "*")

[JoeM]

Doesn't

Like [Forms]![Custom_Reports]![txtClient] & "*"

work without the need for and IIF statement?
The & "*" is usually added to handle the NULL criteria.

[rpeare]

Joe's solution will work for pretty much everything except if you are entering a string and you want to find any record that contains a string.

In that case joe's solution will find everything STARTING with the string provided if you want to show all records where the string occurs anywhere in the search field you'd need

like "*" & forms![custom reports]![txtclient] & "*"

[JoeM]

Joe's solution will work for pretty much everything except if you are entering a string and you want to find any record that contains a string.

Not really my solution, I was just modifying the attempted solution that they posted, but that does make a good point.

[mike760534211]

Joe's solution will work for pretty much everything except if you are entering a string and you want to find any record that contains a string.

In that case joe's solution will find everything STARTING with the string provided if you want to show all records where the string occurs anywhere in the search field you'd need

like "*" & forms![custom reports]![txtclient] & "*"

this causes an overflow if i dont put any criteria in the txt client form field. and if i do enter criteria into the txtclient field on the form i get the same error.

Like [Forms]![Custom_Reports]![txtClient] & "*"

also gives overflow and crashes as well.

Both options give the same error 2950 and the databases location is a trusted location.

[GAtkins]

Like "*" & [Forms]![CorpBondSearch]![SecName] & "*"