Selecting 60 days ago from text date field

[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.

 

[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())

 

[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.

 

[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.

 

[Guest]

Using your example, Fred's post, and because it's a text box, try

Format(DateAdd("d",-60,Date()),"yyyymmdd")

 

[Guest]

Worked !!! Thanks to everyone for your help !!