Wild Card In Criteria

[Brad]

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

 

[Michel Walsh]

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

 

[Brad]

Perfect!

Thanks so much for your help,

Have a great day,

Brad

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.

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

 

[John Vinson]

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

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]