Supply Null value in IIF statement as a condition response

[kevvan]

I have a form that calls a query and passes a textbox value to be evaluated in an IIF statement. The textbox value default value is the word blah.

The IIF statement looks something like this:

IIF ([Forms]![Form1]![txtbox1]="blah", NULL, [Forms]![Form1]![txtbox1])

The logic is that if the user did not enter anything in the text box the condition will be true and the query will be passed NULL which is nothing. That will result in all of the records being shown for that column (which is what I'm trying to achieve!)

The problem is this. The NULL value for the True part of the IIF Function is invalid. I've tried double quotes "", putting nothing in there, LIKE *, IS NOT NULL, and a host of other things but nothing seems to equal a blank value.

I want the truepart of the expression to be nothing (blank). Is that possible?

 

[RivetHed]

Does it work if you pass it a zero length string:

 

[dhookom]

The only way to match a null value in a query is with the expression:

    [SomeField] Is Null

You can 'fix' this with an expression like

    [SomeField] & "" = ""

WHERE [SomeField] & "" = IIF([Forms]![Form1]![txtbox1]="blah", "", [Forms]![Form1]![txtbox1])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kevvan]

Sorry, I think I stated this wrong.

When I say that I want to have a blank criteria, I mean I'd want to replace/remove the criteria [Forms]![Form1]![txtbox1] that exists in the query so it won't filter anything. There's no way for me to do that programmatically. That means that [Forms]![Form1]![txtbox1] has to register a value that's going to give me what I want - all records or the user's choice. In short I'm providing them with a filter. If they don't choose they get everything. If they choose, it matches what they've selected.

The "". does not work.

Stepping away from the component of passing a value in, if the criteria in the query is ["". then the result set would match records that had no value in it. I actually want to return all of the records and would want to pass Like *.

The challenge is that the truepart of the IIF condition will not take LIKE *.

This doesn't work:
Iif (Condition, Like "*"., Falsepart)

There must be some way to have the criteria in the express use one of two conditions

Show all records or the value of [Forms]![Form1]![txtbox1]


I tried to get creative and set the default value of txtbox1 to 'Like "*"' thinking I could pass it in that way.

I wrote the IIF like this:
IIF (txtbox1='Like "*"', txtbox1, txtbox1). It didn't work. It probably interpreted it as a string "LIKE *".

I'm a relative Noob at this so I'll try some of this kooky stuff.

 

[dhookom]

You can't stick the operator (Like) inside the IIf(). Try

WHERE [SomeField] & "" Like IIF([Forms]![Form1]![txtbox1]="blah", "*", [Forms]![Form1]![txtbox1])

If you want to match records containing the controls value in the field value, try:

WHERE [SomeField] & "" Like "*" & IIF([Forms]![Form1]![txtbox1]="blah", "", [Forms]![Form1]![txtbox1]) & "*"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kevvan]

It took a while but I found the answer...in a microsoft Q article:

http://support.microsoft.com/kb/304428/EN-US/

It involves using a combination of the Like Operator and the FORMS parameter.

The answer is this:
Like ([Forms]![Form1]![txtbox1]="blah", "*", [Forms]![Form1]![txtbox1])

If the default value is matched, it builds Like *. If the default value is not matched, it builds Like [Forms]![Form1]![txtbox1] and that matches what the user has selected.

Such an easy solution to a problem that was hard to figure out.

Thanks anyway for the response dhookhom.

 

[kevvan]

I meant dhookom - sorry bout that.

 

[dhookom]

Keep in mind that Like "*" will not match null values. That's why I suggested what I did.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

In the criteria cell:
=[Forms]![Form1]![txtbox1] OR [Forms]![Form1]![txtbox1]='blah'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886