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