IIF Value Error

[TooManyReports]

I am using the follow IIF statement and get zero results back - IIf([Forms]![Basic Form]![Pricing Region]="*",True,[Forms]![Basic Form]![Pricing Region]). If I change the True to 15, I get the information from Region 15 to come back, the result I need is all numbers to come back. I have tried >0 and get 0 results and between 0 and 999 and still get 0 results. Any suggestions??

 

[John Big Booty]

Firstly welcome to the forum.

If you want a wild card in your query, you will need to use the Like operator. Try;

IIf([Forms]![Basic Form]![Pricing Region]="*",[B][COLOR="Purple"]Like "*"[/COLOR][/B],[Forms]![Basic Form]![Pricing Region]

 

[SteveSchapel]

TooManyReports,

I would normally enter the criteria in cases like this as follows:

[Forms]![Basic Form]![Pricing Region]="*" Or [Forms]![Basic Form]![Pricing Region]

 

[TooManyReports]

Thanks guys for all the replys.The Like "*" was not getting any information either and using the OR option was not working neither. The OR option was coming up with an conflict error, I guess it was because I had 12 other IIF and LIKE statements in my query. In the end, I had to use my table field instead.

IIf([Forms]![Basic Form]![Pricing Region]="*",TABLE![Pricing Table].Region,[Forms]![Basic Form]![Pricing Region]

 

[namliam]

How about:
Like [Forms]![Basic Form]![Pricing Region]

Things like this though are why people use search forms, where you build the sql yourself in code. Because this is just asking for it to break, cause problems, headaches, migranes, and worse.

 

[SteveSchapel]

TooManyReports,

I know you seem fixed on using an IIf() function in this case, but just curious, did you try the approach I suggested? I would never use IIf in such cases myself.

 

[John Big Booty]

Your control [Pricing Region] is it a text box or a ComboBox?

If it is a ComboBox does the bound column actually hold the value "*"? If you answer Yes and then No, you will need to replace the "*", in our IIf(), with the value equivalent to "*".