Date Query

[Guest]

In Access 2003, I want to make a query that selects records based on a date.
I have a form with a combo box labeled: "All Records Since:" The choices are
Yesterday, One Week Ago, Two Weeks Ago, One Month Ago. Depending on which is
chosen, I want >Date()-2, >Date()-8, >Date-15, and >Date()-32 to be the
Criterion for the query.

But I can't make it work. If I use the IIF function, the query returns no
records at all (maybe I'm writing it wrong). If I reference the bound column
on the form, Access says the expression is too complex to be evaluated.

Can anybody help? I am open to doing it any old way at all, but I don't
know a whole lot of VBA.

 

[Eric D via AccessMonster.com]

One Option:
Redo your combo box, adding two columns - you can hide the second column.
First column - as you have it now - lists the choices.
Second colum - contains the DateAdd function for each item selected.
Example:
Yesterday would be - DateAdd("d", -1, Date())
1 week ago would be - DateAdd("ww"-1,Date())

When the user makes a selection, pass column two to your query.

Another Option:
Stay with your current combo box but add code to define the DateAdd
parameters to feed your query.

 

[Guest]

Thanks Eric, but the DateAdd function gave me the same wrong results as my

Date()-n. But, alas, I did finally solve it. What I did was put in the

second bound column, the numbers 1, 8, 15, and 32 to correspond to yesterday,
last week, 2 weeks ago, and one month ago and then I referenced that number
in my >Date()-n function. Thanks for the help anyway.