Query dates less than today?

[thudson]

I am trying to write a query to list any items whoes dates are less than today.
The following expression evaluates whether the expression is true or not and gives either 0 or -1 as the result.

Expr1: ([Date relisted]+30)<Now()

If I put either false or even -1 in the criteria it does not evaluate the expression, but gives all the items.
I don't know if I'm being really thick today, but I can't see what I am doing wrong.
Please help!

 

[plog]

I am trying to write a query to list any items whoes dates are less than today.
...
Expr1: ([Date relisted]+30)<Now()

No where did you mention 30 days from today, so I am unclear why you have used that in your expression. Also, the logic doesn't need to go into what is displayed, you just need the field itself and then use the criteria area to filter it. In design view this is how you accomplish your stated goal:

1. Bring down [Date relisted] into your query.
2. Underneath it, in the criteria section put <Date()


That's it, that will filter your query as you stated you wanted.

 

[Pat Hartman]

What plog didn't mention was that Date() is the function that returns ONLY date and Now() is the function that returns today's date PLUS the current time so when you write:

Where SomeDate < Now()

This will select all values for today which are earlier than the current time as well as dates prior to today which I don't think is what you want.

Do a search for Access VBA functions by type so you can review all available functions to find out how they work. This is an invaluable reference to people new to Access.

 

[thudson]

Hi
Got it to work using this expression:

Between [Forms]![Home]![Date_Test1] And [Forms]![Home]![Date_Test2]

What I did not explain before is that I wanted to check if any of my listings on eBay had been relisted and had not missed any.
The criteria above was on a calculated field which added 30 days to the date I entered when it was relisted.

Listed Until: [Date relisted]+30

Thanks for your help.