IIF and wildcards for criteria

[Trocergian]

Is it possible to use a wildcard in an IIF statement as criteria for a query?

I want to check a field, and if it's null then use a "*" for the search criteria but I can't seem to get it to work.

Here's an example of what I've tried:

IIf([Forms]![frmDrawingLocator]![txtPN03] Is Null,"*",[Forms]![frmDrawingLocator]![txtPN03])

I've tried all sorts of variations for "*" but nothing seems to work. Is there any kind of work around I could use to accomplish this?

 

[KenHigg]

I think you need to work a 'Like' statement in there somewhere. Try these:

IIf([Forms]![frmDrawingLocator]![txtPN03] Is Null,Like "*",[Forms]![frmDrawingLocator]![txtPN03])

or

IIf([Forms]![frmDrawingLocator]![txtPN03] Is Null,"Like '*'",[Forms]![frmDrawingLocator]![txtPN03])

 

[Brianwarnock]

In the design grid code

Field: [FieldName]=[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null

Show: uncheck

Criteria: True

Brian

 

[Trocergian]

Yeah, those are some of the exact variations I've tried, but no joy. I;ve even tried using the wildcard in combo with known records "45P*" but it won't find any results. I'm beginning to think it's just not possible to use wildcards with the IIF method. That's why I was also asking if anyone could think of a work around perhaps.

 

[thingssocomplex]

Try putting the Like command outside of the iif statement

Like IIf([Forms]![frmDrawingLocator]![txtPN03] Is Null,Like "*",[Forms]![frmDrawingLocator]![txtPN03])

 

[Trocergian]

Brain nailed the solution - worked perfectly.

Thank you!

Came while I still have some hair I didn't pull out yet. Excellent!

(I just wish I understood exactly what it was doing)

 

[Brianwarnock]

Search on query by form and threads started by JONK in the advanced search, an explanation and more info there.

Brian

 

[Brianwarnock]

Sorry it is Jon K, anyway this is it

Brian

 

[KenHigg]

Thats confusing... Do you understand whats going on Brian?

 

[Brianwarnock]

Thats confusing... Do you understand whats going on Brian?

Of course, I'm brilliant, don't you know.

Erm! What's confusing Ken, Ken?

Brian

 

[KenHigg]

Well, I kind of see the first x=x thing returning a true/false but how does the or thing work?

 

[Brianwarnock]

I take it you are talking about my post.It is along time since I read Jon k's marvelous thread, but I think that it works like this.

The TRUE in the criteria row tells Access to treat the field row as criteria and thus if either expression is true that is what is acted on, thus if the fieldname=controlname that is what is selected, and if the control is null then there is no criteria and all are selected.

Hope that helps. Have you read the thread?

Brian

 

[KenHigg]

Yes I read Jons thread but I'm still confused. The x=y thing will return a true or false. Then the Is Null thing will return a true or false. So the or is if either of these meet the 'true' criteria then it passes the test. I've just never seen it that way. I would have done the x=y in one column and the Is Null in a second column and put true as criteria in each one.

 

[Brianwarnock]

Well ken you could try it and see, In the example DB from Jon we have the following and perhaps the SQL shows it better

Field in Design Grid
[MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null

Crieria has True

SQL
(([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null)=True)


Brian