Wild Card In Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 28 Nov 2005 16:05 GMT

Thanks for taking the time to read my question.

I have a field in a query that I want to apply an iif statement to in the
criteria. If cboX = "Blank" then "*" else cboX

CODE
-------
IIf([Forms]![frmReportGenerator]![cbo_ProdNumCM]="Blank","*",[Forms]![frmReportGenerator]![cbo_ProdNumCM])
--------

I can't get this to work

I've seen it done before, but can't remember where.

Can the logic also be explained, so I can remember for next time?

Thanks again for your help.

Brad

Reply to this Message

Michel Walsh - 28 Nov 2005 16:23 GMT

Hi,

If the form is opened, and want to use * if the control get the value
"Seattle" in it, use:

LIKE
IIf([Forms]![frmReportGenerator]![cbo_ProdNumCM]="Seattle","*",[Forms]![frmReportGenerator]![cbo_ProdNumCM])

If you want use * if the control has nothing in it, use:

LIKE IIf([Forms]![frmReportGenerator]![cbo_ProdNumCM IS
Null,"*",[Forms]![frmReportGenerator]![cbo_ProdNumCM])

You were just missing the operator LIKE. Access was unable to determine if
you want use = ,or LIKE. In those case, you have to specify the
operation. While sometimes Access can find which one is required, based on
the context, it is preferable to specify the one we want, to avoid surprises

:-)

And unless you want to spot the string "Blank", you use IS NULL, not
="Blank"... just in case something decided to have Blank, as name!

Hoping it may help,
Vanderghast, Access MVP

> Thanks for taking the time to read my question.
>
[quoted text clipped - 15 lines]
>
> Brad

Reply to this Message

Brad - 28 Nov 2005 17:01 GMT

Perfect!

Thanks so much for your help,

Have a great day,

Brad

> Hi,
>
[quoted text clipped - 40 lines]
> >
> > Brad

Reply to this Message

John Vinson - 30 Nov 2005 18:01 GMT

>Thanks for taking the time to read my question.
>
[quoted text clipped - 5 lines]
>IIf([Forms]![frmReportGenerator]![cbo_ProdNumCM]="Blank","*",[Forms]![frmReportGenerator]![cbo_ProdNumCM])
>--------

Two suggestions. Use a query criterion of

LIKE IIF(<your expression>)

Or - probably better - use instead a criterion

WHERE fieldname = [Forms]![frmReportGenerator]![cbo_ProdNumCM] OR
[Forms]![frmReportGenerator]![cbo_ProdNumCM] = "Blank"

This asssumes that the Bound Column of the combo box contains either
the proper value of ProdNumCM (which must be a Text field for this to
work) or else the five-letter text string "Blank". If you want to use
a NULL value for the combo instead of a text string, your second
criterion should be

[Forms]![frmReportGenerator]![cbo_ProdNumCM] IS NULL

rather than ="Blank".

John W. Vinson[MVP]

Reply to this Message