iif stmt as criteria in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code as criteria in a query. It will work if
CmbCustomer is not null - it filters as it should. But if CmbCustomer is
null I get nothing returned. I need to see all records if CmbCustomer is
null. I have tried:

IIf([Forms]![FrmShippedParameters]![CmbCustomer] Is Not
Null,[Forms]![FrmShippedParameters]![CmbCustomer],Is Not Null Or Is Null)

and

IIf([Forms]![FrmShippedParameters]![CmbCustomer] Is Not
Null,[Forms]![FrmShippedParameters]![CmbCustomer])

Any ideas??
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try the Nz() function:

[Customer] = Nz([Forms]![FrmShippedParameters]![CmbCustomer],[Customer])

If [Customer] was a column in the table you're querying the Nz()
function would return the value in the Customer column when the ComboBox
"cmbCustomer" was null. This, in effect, means return all rows.

Substitute the "real" column for [Customer].

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

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

iQA/AwUBRDMRtoechKqOuFEgEQIIWwCfbpyK17jadYHkXYfL7QuvwmIbrBgAoN3f
OzLejRpgegjUhr7Ha7ucbJU1
=TbMj
-----END PGP SIGNATURE-----
 
Thank you, thank you, thank you :)

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

Try the Nz() function:

[Customer] = Nz([Forms]![FrmShippedParameters]![CmbCustomer],[Customer])

If [Customer] was a column in the table you're querying the Nz()
function would return the value in the Customer column when the ComboBox
"cmbCustomer" was null. This, in effect, means return all rows.

Substitute the "real" column for [Customer].

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

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

iQA/AwUBRDMRtoechKqOuFEgEQIIWwCfbpyK17jadYHkXYfL7QuvwmIbrBgAoN3f
OzLejRpgegjUhr7Ha7ucbJU1
=TbMj
-----END PGP SIGNATURE-----

I have the following code as criteria in a query. It will work if
CmbCustomer is not null - it filters as it should. But if CmbCustomer is
null I get nothing returned. I need to see all records if CmbCustomer is
null. I have tried:

IIf([Forms]![FrmShippedParameters]![CmbCustomer] Is Not
Null,[Forms]![FrmShippedParameters]![CmbCustomer],Is Not Null Or Is Null)

and

IIf([Forms]![FrmShippedParameters]![CmbCustomer] Is Not
Null,[Forms]![FrmShippedParameters]![CmbCustomer])

Any ideas??
Click to expand...
Click to expand...
 
Back
Top