slow queries

[Alex]

I need to show records according to fiscal year the 4 last
years and for the 4 last months.

The fiscal year is from Aug 1 to Jul 31.

I'm using the following to choose years:

In the query design in the Field:
Year(DateAdd("m",+5,[Day]))
in the Criteria: Format$(Date(),"yyyy")-4 for the last 4th
year and in other queries -3, -2, -1.

To get a month I'm using two columns:
1st. In the Field: Format([Day],"yyyy") and in Criteria:
Format$(Date(),"yyyy") - to take the current year

2d. In the Field: Format([Day],"mm") and in the Criteria:
Format$(DateAdd("m",-4,Date()),"mm") to take the last 4th
month (the rest of months -3, -2, -1).

Everything is working except the queries is very slow.
Using straightforward >=#8/1/2002# And <=#7/31/2003#
makes it much more faster but I'd like to have it flexible.

Could anybody advise anything?

Thanks

 

[Alex]

For a month I'm using >=DateSerial(Year(Date()),Month(Date
())-1,1) And <DateSerial(Year(Date()),Month(Date()),1)
It's working good now.

What would you advise to use for the fiscal years?

Thanks

 

[John Spencer (MVP)]

The difference in speed is because the SQL doesn't have to evaluate every field,
but can use the index. Try changing the criteria.

For records for last 4 months:
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), Month(Date())-4, 1) AND
DateSerial(Year(Date()), Month(Date()),0)

For Today's date of June 23, 2004 that should give you records from Feb 1, 2004
to May 31, 2004

For Fiscal years

Field: YourDateField
Criteria: Between DateSerial(Year(Date())-4,8,1) AND
DateSerial(Year(Date())-3,7,31)

So that would get 2004 minus 4 = 2000 and minus 3 = 2001 which gets all records between
8/1/2000 and 7/31/2001.

You can even use parameters in place of the subtraction number.
Between DateSerial(Year(Date())-[Years Back],8,1) AND
DateSerial(Year(Date())-[Years Back]+1,7,31)

When prompted for Years Back enter 4 or 3 or 2 or 1 or even zero and you should
get a particular year's worth of data.

 

[Alex]

Thanks a lot, John.

Alex

-----Original Message-----
The difference in speed is because the SQL doesn't have to evaluate every field,
but can use the index. Try changing the criteria.

For records for last 4 months:
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), Month(Date())- 4, 1) AND
DateSerial(Year(Date()), Month(Date()),0)

For Today's date of June 23, 2004 that should give you records from Feb 1, 2004
to May 31, 2004

For Fiscal years

Field: YourDateField
Criteria: Between DateSerial(Year(Date())-4,8,1) AND
DateSerial(Year(Date())-3,7,31)

So that would get 2004 minus 4 = 2000 and minus 3 = 2001 which gets all records between
8/1/2000 and 7/31/2001.

You can even use parameters in place of the subtraction number.
Between DateSerial(Year(Date())-[Years Back],8,1) AND
DateSerial(Year(Date())-[Years Back]+1,7,31)

When prompted for Years Back enter 4 or 3 or 2 or 1 or even zero and you should
get a particular year's worth of data.

I need to show records according to fiscal year the 4 last
years and for the 4 last months.

The fiscal year is from Aug 1 to Jul 31.

I'm using the following to choose years:

In the query design in the Field:
Year(DateAdd("m",+5,[Day]))
in the Criteria: Format$(Date(),"yyyy")-4 for the last 4th
year and in other queries -3, -2, -1.

To get a month I'm using two columns:
1st. In the Field: Format([Day],"yyyy") and in Criteria:
Format$(Date(),"yyyy") - to take the current year

2d. In the Field: Format([Day],"mm") and in the Criteria:
Format$(DateAdd("m",-4,Date()),"mm") to take the last 4th
month (the rest of months -3, -2, -1).

Everything is working except the queries is very slow.
Using straightforward >=#8/1/2002# And <=#7/31/2003#
makes it much more faster but I'd like to have it flexible.

Could anybody advise anything?

Thanks

.