Selecting 60 days ago from text date field


G

Guest

I would like to select rows from a table where a text date field is greater
than 60 days before the current date. Can you help with the expression ?
Thx.
 
Ad

Advertisements

F

fredg

I would like to select rows from a table where a text date field is greater
than 60 days before the current date. Can you help with the expression ?
Thx.

By 'greater than 60 days' I assume you mean older than 60 days.
As criteria on the date field in your query, either

< Date()-60

Or..
< DateAdd("d",-60,Date())
 
G

Guest

If the "date" is in a text field, you need to convert it to a date first.
This can be a problem if someone types in something like 13/13/2006. So first
we need to test for the ability of Access to evaluate it as a date; convert
it to a date if so; deal with it if not a valid date. Put the following IIf
statement in the heading of a query and change "YourTextField" in both places
to the actual field name.

Text2Date: IIf(IsDate([YourTextField])=True, CDate([YourTextField]),
Date()-61)

Then fredg's < Date()-60 in the criteria will work.

Realize that the Date()-61 will show all records that are not evaluated as a
valid date to show up as overdue. This will include null records.

Also IsDate and CDate see 5/1/2006 and May 1st of 2006. If you are using
the non-USA Day/Month/Year, you will have problems.
 
G

Guest

Actually, I meant 60 days ago thru the present. (sorry)

Can't seem to get either of your suggestions to work (no data is returned).
If I enter > "20060430" in the criteria, it works. Of course, I don't want
to have to change the query each day, so I would like to use (current date -
60) - somehow.
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top