Dates in Query

[James]

Hello I was wondering if I set up a query and I wanted it
to look at the current date I would do Date(). Am I right
with this?

If so then how would I get the review date in my database
to look at the current date and run the query on the
Criteria of a week before this review date.

How Would I do this?

Many Thanks

James

 

[fredg]

Hello I was wondering if I set up a query and I wanted it
to look at the current date I would do Date(). Am I right
with this?

If so then how would I get the review date in my database
to look at the current date and run the query on the
Criteria of a week before this review date.

How Would I do this?

Many Thanks

James

Add another column to the query.
ExpatePart("ww",[YourDateField])

Then as criteria for this column:
=DatePart("ww",Date())-1

You don't have to actually display this column.
Uncheck the Show check box.

Today, 4/19/2004, is week 17.
The above will return records of week 16, 4/12 to 4/17/2004.

 

[fredg]

Hello I was wondering if I set up a query and I wanted it
to look at the current date I would do Date(). Am I right
with this?

If so then how would I get the review date in my database
to look at the current date and run the query on the
Criteria of a week before this review date.

How Would I do this?

Many Thanks

James

Add another column to the query.
ExpatePart("ww",[YourDateField])

Then as criteria for this column:
=DatePart("ww",Date())-1

You don't have to actually display this column.
Uncheck the Show check box.

Today, 4/19/2004, is week 17.
The above will return records of week 16, 4/12 to 4/17/2004.

James,
In re-reading this reply I realized that it would return no records if
the current week of the year was the first week, as there is no week
0.

Change the query to the following:

ExpatePart("ww",[DateField])

and as criteria, write
=DatePart("ww",Date()-7)

 

[John Vinson]

Hello I was wondering if I set up a query and I wanted it
to look at the current date I would do Date(). Am I right
with this?

Probably... but there's a glitch. If your datefield has a time
component, i.e. it's filled using the Now() function, you won't get
anything; a criterion of

= Date() AND < Date() + 1

will work however.

If so then how would I get the review date in my database
to look at the current date and run the query on the
Criteria of a week before this review date.

A Criterion of

BETWEEN Date() AND Date() + 7

will return all reviewdates in the upcoming week.

 

[James]

Many Thanks to both of you...

It works like a treat...

James

-----Original Message-----

Hello I was wondering if I set up a query and I wanted it
to look at the current date I would do Date(). Am I right
with this?

If so then how would I get the review date in my database
to look at the current date and run the query on the
Criteria of a week before this review date.

How Would I do this?

Many Thanks

James

Add another column to the query.
ExpatePart("ww",[YourDateField])

Then as criteria for this column:
=DatePart("ww",Date())-1

You don't have to actually display this column.
Uncheck the Show check box.

Today, 4/19/2004, is week 17.
The above will return records of week 16, 4/12 to

4/17/2004.

James,
In re-reading this reply I realized that it would return no records if
the current week of the year was the first week, as there is no week
0.

Change the query to the following:

ExpatePart("ww",[DateField])

and as criteria, write
=DatePart("ww",Date()-7)

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.