Searching a field for a value using IIF

[BA777]

Guys,

In my database report I want to search the Product Title field for a * at the end of it. Then if the mentioned product has the *, I want to display the description of that product. At the moment I have the following IIF statement, but it shows every single description, how can i narrow it down to just the ones I want?

=IIf([Product Title] And "*",[Product Description],Null)

Thanks for any help

Henry

 

[Moniker]

=IIf([Product Title] & "*",[Product Description],Null)

However, I'd go way against recommending storing an asterisk in the field, as the asterisk is also the wildcard symbol, making your results questionable at best.

Edit: A cleaner way, assuming the asterisk is always at the end of the Product Title field:

=Iif(Right([Product Title],1)="*",[Product Description],Null)

I'd still avoid the asterisk for the reasons I mentioned above.

 

[PatrickJohnson]

can this be used as a variable search? like a prompt which asks for the string you are looking for and returns all records with that string in a certain field?

 

[Moniker]

To do that:

=IIf([Product Title] Like [StringTheyInput] & "*",[Product Description],Null)

 

[BA777]

Thanks guys, say i changed the * in the field to a ^ then which function should i best go for to get the best results?

 

[Moniker]

=Iif(Right([Product Title],1)="^",[Product Description],Null)