Like IIF Statement in Query

[Bill Foley]

Access XP in Windows 2000

I have a query that runs a report based on combo box results. I am using
the Like IIF statement to set the criteria. If I leave one particular combo
box empty or click the button that doesn't filter any information, some of
the records aren't showing up in the report. These relate to the fields
that don't have values in them. I was thinking that using the "*" in the
IIF statement would include all if nothing is selected. Obviously not.

An example of a statement is:

Like
IIf(IsNull([Forms]![frmMyForm]![cboMyCombo]),"*",[Forms]![frmMyForm]![cboMyC
ombo])

Any ideas how to include records that have do data in certain fields that
have the ability to be filtered in the combo box?

 

[Ken Snell [MVP]]

Use this criterion expression instead of your IIf one:


[Forms]![frmMyForm]![cboMyCombo] Or [Forms]![frmMyForm]![cboMyCombo] Is Null

 

[John Vinson]

Like
IIf(IsNull([Forms]![frmMyForm]![cboMyCombo]),"*",[Forms]![frmMyForm]![cboMyC
ombo])

Any ideas how to include records that have do data in certain fields that
have the ability to be filtered in the combo box?

Try instead using a criterion of

= [Forms]![frmMyForm]![cboMyCombo] OR [Forms]![frmMyForm]![cboMyCombo]
IS NULL


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

WHERE column_name LIKE Nz([Forms]![frmMyForm]![cboMyCombo],"*")

If you want to use the equal sign:

WHERE (IS NULL [Forms]![frmMyForm]![cboMyCombo]
OR column_name = [Forms]![frmMyForm]![cboMyCombo])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYkmMIechKqOuFEgEQIsRwCdEURN1XYmdYpSiWu1gUh6ZbgHv7oAn1UK
o/GTOOmD7tvVfcXUAmbA9rzt
=lQ1Q
-----END PGP SIGNATURE-----

 

[Bill Foley]

Thanks all! Works like a champ. Sorry for the delay is posting back.
Getting pretty hectic around here. I appreciate it!

--
Bill Foley

Use this criterion expression instead of your IIf one:


[Forms]![frmMyForm]![cboMyCombo] Or [Forms]![frmMyForm]![cboMyCombo] Is Null
--

Ken Snell
<MS ACCESS MVP>

Access XP in Windows 2000

I have a query that runs a report based on combo box results. I am using
the Like IIF statement to set the criteria. If I leave one particular combo
box empty or click the button that doesn't filter any information, some of
the records aren't showing up in the report. These relate to the fields
that don't have values in them. I was thinking that using the "*" in the
IIF statement would include all if nothing is selected. Obviously not.

An example of a statement is:

Like

IIf(IsNull([Forms]![frmMyForm]![cboMyCombo]),"*",[Forms]![frmMyForm]![cboMyC

ombo])

Any ideas how to include records that have do data in certain fields that
have the ability to be filtered in the combo box?