Date Query

[Tezdread]

Hi all,

Could someone advise on the correct query criteria I need to use to determine which records are between 4 and 8 weeks old?

 

[PK2000]

I think if your specifying which dates you want your query to be between the criteria Between #Date# And #Date# should work.
An example would be Between #2002-01-01# And #2002-12-31#
This brings up all my dates from 2002. You can change the dates to the ones your trying to query.
Hope this helps.

 

[Tezdread]

I don't think the Between Date1 And Date2 will work but I didn't explain the situation properly...

In the table I have a Date In field which always has a date

There is another date field called Date Resolved and this only has a date when the case is closed.

What I need the query to do is work out all those records that are open (no date in Date Resolved) and have been open between 4 and 8 weeks (28 - 56 days)

Example:
Record1 was entered on 1-1-00
The query is run on 29-1-00

In this example Record1 wouldn't be shown

Example:
Record2 was entered on 10-1-00
Query was run on 5-2-00

In this example Record2 would be shown

Hope this makes it clearer

 

[Mile-O]

Between DateAdd("d", -56, Date()) And DateAdd("d", -28, Date())

 

[zdog]

A select query should do the trick
select the date in, date resolved, and any other fields that you wish to be display.
No set the criteria for the date resolved to Is null,
this will address the first issue of selecting only open cases.
Now create a new field in the query, let says week_num
week_num: DateDiff("ww",[Date In], date()), this will calculate the difference between the date the case was opened and the current date in weeks.
Set the criteria for this field to Between 4 and 8, to return only those open cases that are 4 to 8 weeks old.

This should produce the result you are looking for.
ZDog

 

[Tezdread]

thanks guys.

SJ that code is spot on and I can understand what it's doing so will be able to remember and reuse it...always a good thing!

cheers