By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,215 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,215 IT Pros & Developers. It's quick & easy.

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

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. HAVING (((NADTble.WE_DT)=IIf([Forms]![frmMoSel]![CBX_WED] = " ",Date()-21,[Forms]![frmMoSel]![CBX_WED])) AND ...
any help is appreciated
May 13 '09 #1
Share this Question
Share on Google+
3 Replies

Curtis Rutland
Expert 2.5K+
P: 3,256
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.

May 13 '09 #2

Expert 100+
P: 489
Expand|Select|Wrap|Line Numbers
  1. Date()-21
should be
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![frmMoSel]![CBX_WED]>"",[Forms]![frmMoSel]![CBX_WED], >=DateAdd("d",-21,Date()))
May 13 '09 #3

Expert Mod 15k+
P: 31,707
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).
May 13 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.