Query criteria

[JonoB]

I have a combobox on a form that I want to use as the criteria for a query. The combobox rowsource is as follows:

SELECT [TopicID],[Topic] FROM tblSiteTopic UNION Select 99 as AllChoice, "(All)" as Bogus from tblSiteTopic ORDER BY [Topic];

Essentially, the Union part allows me to select "(All)" as an option, which is correlated with the number 99. I have tested this to make sure that the correct number is derived from the combobox by using a msgbox.

I also have a subform, which is based on a query, and this query uses the value from the combobox as a criteria.

This pretty much works fine, with one small problem : the query works fine for any value, other than 99. When I select "(All)" as an option, then no records are returned in the query. The sql for my query is as follows:

SELECT [MailID], [SiteID], [SiteTopic], [MailDate], [MailSentBy], [MailSubject], [MailDescription], [MailLink], [MailUser]
FROM tblSiteMail
WHERE ((([SiteTopic])=IIf([Forms]![frmSite]![cboSiteTopic]=99,(tblSiteMail.SiteTopic)<99,[Forms]![frmSite]![cboSiteTopic])))
ORDER BY tblSiteMail.MailDate DESC;

I have also tried modifying the WHERE clause as follows, but still no luck
WHERE ((([SiteTopic])=IIf([Forms]![frmSite]![cboSiteTopic]=99, Is Not Null,[Forms]![frmSite]![cboSiteTopic])))

Once again, both of these options work fine if [Forms]![frmSite]![cboSiteTopic] is anything other than 99 (or whatever value is assigned to "(All)")

Any help much appreciated.

 

[JonoB]

Oh, and I must add that if I change the WHERE clause to

WHERE (((tblSiteMail.SiteTopic)<99))

or to

WHERE (((tblSiteMail.SiteTopic) Is Not Null))

then, the query runs fine!

 

[scriverb]

Remove the RED code below:

WHERE ((([SiteTopic])=IIf([Forms]![frmSite]![cboSiteTopic]=99,[b][red]([/red][/b]tblSiteMail.SiteTopic[b][red])<99[/red][/b],[Forms]![frmSite]![cboSiteTopic])))
ORDER BY tblSiteMail.MailDate DESC;

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JonoB]

Thank you very much, works great.