Date Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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
Click to expand...
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.
 
Back
Top