null fields part 2

[bbdobuddy]

Hi

In response to someones request here is the SQL

SELECT DDM2.ID, DDM2.ItemNumber, DDM2.Sudocstem, ItemLister.Cataloging,
MarciveProfile.CatalogingRequested, DDM2.Format, DDM2.Title
FROM ((DDM2 LEFT JOIN ItemLister ON DDM2.ID = ItemLister.ID) LEFT JOIN
MarciveProfile ON DDM2.ID = MarciveProfile.ID) LEFT JOIN Superseded ON
DDM2.ID = Superseded.ID
WHERE
(((ItemLister.Cataloging)=[Forms]![frmAgencyCode&IL&SupersededList]![cmbILInput])
AND ((DDM2.AgencyCode)=[Forms]![frmAgencyCode&IL&SupersededList]![cmbAInput])
AND
((Superseded.ItemNumber)=IIf([Forms]![frmAgencyCode&IL&SupersededList]![cmbSuperInput]="No",([Superseded].[ItemNumber])
Is Null,([Superseded].[ItemNumber]) Is Not Null)));

Is there something wrong with the last part of the WHERE statement
I am trying to say if the combobox value is "No" then I want to check for
null fields otherwise I want to check for all non null fields

Any help would be great

 

[bbdobuddy]

I got another problem it says that
((Superseded.ItemNumber)=IIf([Forms]![frmAgencyCode&IL&SupersededList]![cmbSuperInput]="No",([Superseded].[ItemNumber])
Is Null,([Superseded].[ItemNumber]) Is Not Null)));

Is too complicated how do I fix that?

 

[MGFoster]

I got another problem it says that
((Superseded.ItemNumber)=IIf([Forms]![frmAgencyCode&IL&SupersededList]![cmbSuperInput]="No",([Superseded].[ItemNumber])
Is Null,([Superseded].[ItemNumber]) Is Not Null)));

Is too complicated how do I fix that?

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

Hi there, me again...

You don't need this in the WHERE clause:

... Superseded.ItemNumber)=IIf(...

Your WHERE clause should look like this:

WHERE
ItemLister.Cataloging=[Forms]![frmAgencyCode&IL&SupersededList]![cmbILIn
ut])
AND
DDM2.AgencyCode=[Forms]![frmAgencyCode&IL&SupersededList]![cmbAInput])
AND
IIf([Forms]![frmAgencyCode&IL&SupersededList]![cmbSuperInput]="No",
[Superseded].[ItemNumber] Is Null,
[Superseded].[ItemNumber] Is Not Null)

If you don't want to use the IIf() function try this:

AND ((Forms![frmAgencyCode&IL&SupersededList]!cmbSuperInput="No" AND
Superseded.ItemNumber IS NULL) OR
(Forms![frmAgencyCode&IL&SupersededList]!cmbSuperInput="Yes" AND
Superseded.ItemNumber IS NOT NULL))

The above is more SQL syntax compliant.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQhqtDoechKqOuFEgEQKfMQCcDEuKpn7Eb3vcH+afIJR4c7VGiIwAoIBo
fJuVd6PG3PsBFFCGrYakrl8l
=dFwa
-----END PGP SIGNATURE-----