Selecting 60 days ago from text date field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MS - 30 Jun 2006 15:52 GMT

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.

Reply to this Message

fredg - 30 Jun 2006 15:59 GMT

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

MS - 30 Jun 2006 16:37 GMT

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.

> > 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 ?
[quoted text clipped - 7 lines]
> Or..
> < DateAdd("d",-60,Date())

Reply to this Message

Ofer Cohen - 30 Jun 2006 16:48 GMT

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

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

Signature

Good Luck
BS"D

> Actually, I meant 60 days ago thru the present. (sorry)
>
[quoted text clipped - 14 lines]
> > Or..
> > < DateAdd("d",-60,Date())

Reply to this Message

MS - 30 Jun 2006 17:01 GMT

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

> Using your example, Fred's post, and because it's a text box, try
>
[quoted text clipped - 18 lines]
> > > Or..
> > > < DateAdd("d",-60,Date())

Reply to this Message

Jerry Whittle - 30 Jun 2006 16:29 GMT

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.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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

Reply to this Message