query(static ??)

[macamarr]

Below is my query criteria. What I don't know is how to
make the criteria so it will query the current year of of
the time frame year after year.



This is what I have now

Between #11/01/2003# And #10/31/2004#

This is what I don't know how to do

between #10/31/current year# - 364 days

 

[Les]

You could build an expression for your dates. It looks
like month and day are always the same. You could use a
combination of functions to get the year.
DatePart("yyyy",Date()) will get you this year.
DatePart("yyyy",DateAdd("yyyy",-1,Date())) will get you
last year.

Probably easier ways, but this should help.

 

[John Vinson]

Below is my query criteria. What I don't know is how to
make the criteria so it will query the current year of of
the time frame year after year.



This is what I have now

Between #11/01/2003# And #10/31/2004#

This is what I don't know how to do

between #10/31/current year# - 364 days

The DateSerial function will do the job very nicely:

BETWEEN DateSerial(Year(Date)) - 1, 11, 1) AND DateSerial(Year(Date),
10, 31)

Note that this will still return the range from Nov. 1 2003 through
Oct. 31 2004 even if the query is run in November or December - is
that what you want?

 

[macamarr]

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


The DateSerial function will do the job very nicely:

BETWEEN DateSerial(Year(Date)) - 1, 11, 1) AND DateSerial (Year(Date),
10, 31)

Note that this will still return the range from Nov. 1 2003 through
Oct. 31 2004 even if the query is run in November or December - is
that what you want?


.
That would be fine, this query is to check for perfect

attendance. Will be queried often, but in the end the last
query in of the current year (late October) will be the
one that is most important. Thank You!

 

[Guest]

Thank You! Will give it a go...

-----Original Message-----
You could build an expression for your dates. It looks
like month and day are always the same. You could use a
combination of functions to get the year.
DatePart("yyyy",Date()) will get you this year.
DatePart("yyyy",DateAdd("yyyy",-1,Date())) will get you
last year.

Probably easier ways, but this should help.

.

 

[John Vinson]

attendance. Will be queried often, but in the end the last
query in of the current year (late October) will be the
one that is most important. Thank You!

I guess my concern is that if you use it often during the (academic?)
year, you might be using it in say mid-December expecting to check the
attendance between November 1 and mid-December. It won't; it will
instead check the PREVIOUS year's values. Is that OK? (because the
expression can be modified if not).

 

[macamarr]

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


I guess my concern is that if you use it often during the (academic?)
year, you might be using it in say mid-December expecting to check the
attendance between November 1 and mid-December. It won't; it will
instead check the PREVIOUS year's values. Is that OK? (because the
expression can be modified if not).


.
I think I will be ok, the reason it will only count at

the end of Oct is so that employees with perfect
attendance get a reward at the end of the year. It is
always from the end of Oct To the beginning of Nov of the
next year.