IIF conditionals using static and form entries as a criteria for queries

[Danmop]

Cant seem to get this syntax to work right. Have a form that allows users to enter a week ending date to query on, or they can leave it blank to use the default range of weeks, which is the last 3 weeks from today.
Here is the criteria statement I used:

IIf([Forms]![frmMoSel]![CBX_WED]=" ", >=Date()-21, [Forms]![frmMoSel]![CBX_WED])

default setting in CBX_WED is " ". query works if i enter a date in the form, but I cant get the static portion (ie the 3 week range) of the criteria to work when I leave the form blank. I also tried using the ISNULL condition instead of the " ", set the column as an expression, as a "where" setting, and as a "group by" settings, all with tthe same results, nothing. no errors, just opens and closes with no records showing. here is the sql portion of the syntax

HAVING (((NADTble.WE_DT)=IIf([Forms]![frmMoSel]![CBX_WED] = " ",Date()-21,[Forms]![frmMoSel]![CBX_WED])) AND ...

any help is appreciated

[Curtis Rutland]

This isn't the appropriate forum for technical questions...
Please identify what language (or from the looks of it, which database) you are using so a moderator can move it to the proper forum.

Thanks,
insertAlias
MODERATOR

[DonRayner]

Date()-21

should be

DateAdd("d",-21,Date()),

I would also change up the IIf to check for anything other than a zero length string instead of checking for a zero length string.

IIf([Forms]![frmMoSel]![CBX_WED]>"",[Forms]![frmMoSel]![CBX_WED], >=DateAdd("d",-21,Date()))

[NeoPa]

HAVING (((NADTble.WE_DT)=IIf([Forms]![frmMoSel]![CBX_WED] = " ",Date()-21,[Forms]![frmMoSel]![CBX_WED])) AND ...

I would use WHERE clause rather than HAVING. This is understandable as it's the default provided in Access unless you select Where (in a grouped query).

An empty control will be Null. Not an empty string (""), and never a single space (as you've used).

You appear to be comparing a Date field with a single date 21 days ago, rather than searching for any date after that date (or more flexibly Between two specified dates).