IIf statement to return all values

[sstreet]

I would like the following statement to take an entered value unless it is null. If there is no value entered, I would like to use the * operator to show everything from that field.

IIf([Forms]![SearchForm]![txtinvestment] Is Null,Like "*",[Forms]![SearchForm]![txtinvestment])

Why does this code not work?

 

[FoFa]

There is problem with your IIF:
IIf(ISNULL([Forms]![SearchForm]![txtinvestment]),Like "*",[Forms]![SearchForm]![txtinvestment])

is the proper syntax, let me know if it works in the criteria

 

[Fizzio]

Or

=[Forms]![SearchForm]![txtinvestment] or [Forms]![SearchForm]![txtinvestment] is null

 

[sstreet]

thanks that sorted it!

 

[gbradley]

I Want to something similar to this ...

IIf(ISNULL([Forms]![SearchForm]![txtinvestment]),L
ike "*",[Forms]![SearchForm]![txtinvestment])


I want to have a user input where the "*" is.

IIf(ISNULL([Forms]![SearchForm]![txtinvestment]),Like [Enter Part Number ],[Forms]![SearchForm]![txtinvestment])

when I do it this way, a dialog box comes up whether the field is null or not.
If the field is null it uses the Entered value, but if the field is not null it uses the value from the field.

I don't like them having to hit enter twice.
I ended up using two different reports and a macro using the iif statement to open the correct report.

Is there a better way?

 

[FoFa]

How about:
IIf([Forms]![SearchForm]![txtinvestment] Is Null,Like "*",Like "' & [Forms]![SearchForm]![txtinvestment] & "*'")

 

[Travis]

The problem that you are having is that the IIF calculates all of the statement before it returns a value.

One method to get around this is to use a Function in its place:

Such as:

Public Function GetAnswer(Value as Variant) as String

If ISNULL(Value) Then
GetAnswer="Like " & InputBox("Enter Part Number")
Else
GetAnswer=Value
End If

End Function

 

[gbradley]

Do I use the function in the criteria for the query?

I tried building it, but I get Invalid Syntax errors.

 

[Travis]

Place the code in a public module.

then place the function in the Criteria section of the Query