date criteria

[Skunk]

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

 

[KARL DEWEY]

Between DateSerial([Enter start year (2003)],11,15) AND DateSerial([Enter
end year (2003)],3,31)

 

[Skunk]

Thanks Karl. I wasn't clear enough. I don't want the user to input, I want
to take the system's year as the variable. Thus, given a criteria also of

Date()), I will return all future years' records between 11/15 and 3/31.

Can you point the way and 'splain to me?

Thanks again.

Between DateSerial([Enter start year (2003)],11,15) AND DateSerial([Enter
end year (2003)],3,31)

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

 

[KARL DEWEY]

This give records from last November year through March this year.
Between DateSerial(Year(Date())-1,11,15) AND DateSerial(Year(Date()),3,31)

Or do you want all records from
15 Nov year1 through 31 Mar year2
15 Nov year2 through 31 Mar year3
15 Nov year3 through 31 Mar year4
etc.?

Thanks Karl. I wasn't clear enough. I don't want the user to input, I want
to take the system's year as the variable. Thus, given a criteria also of

Date()), I will return all future years' records between 11/15 and 3/31.

Can you point the way and 'splain to me?

Thanks again.

Between DateSerial([Enter start year (2003)],11,15) AND DateSerial([Enter
end year (2003)],3,31)

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

 

[KARL DEWEY]

If the latter use a calculated field like this --
Expr1: Format([Date open],"y")
This gives you the day of the year.
Then criteria like this --
<IIf(Year([Date open]) Mod 4=0,92,91) Or >IIf(Year([Date open]) Mod
4=0,319,318)

Leap years are evenly divisible by 4. So if not leap year the end of March
is day 90.

This give records from last November year through March this year.
Between DateSerial(Year(Date())-1,11,15) AND DateSerial(Year(Date()),3,31)

Or do you want all records from
15 Nov year1 through 31 Mar year2
15 Nov year2 through 31 Mar year3
15 Nov year3 through 31 Mar year4
etc.?

Thanks Karl. I wasn't clear enough. I don't want the user to input, I want
to take the system's year as the variable. Thus, given a criteria also of

Date()), I will return all future years' records between 11/15 and 3/31.

Can you point the way and 'splain to me?

Thanks again.

Between DateSerial([Enter start year (2003)],11,15) AND DateSerial([Enter
end year (2003)],3,31)

:

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

 

[John W. Vinson]

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

If you want to return records for a given range of dates regardless of the
year, fake it out by creating a calculated field in your Query using the
current year:

SearchDate: DateSerial(Year(Date()), Month([datefield]), Day([datefield]))

This will take a table field containing (say) 5/16/1946 and create a new
datefield 5/16/2009. (Don't fear, it will take 2/29/1968 and return 3/1/2009).

You can then use a criterion on this field of

BETWEEN #3/1# AND #5/31#

If you want to "wrap" around the new year you'll need to do it in two pieces -
BETWEEN #11/15# AND #12/31# OR BETWEEN #1/1# AND #3/31#.

 

[Skunk]

Thanks Karl...I didn't get far in trying this (unsuccessfully) when I saw
John's and tried it. It's what I am after.

Thanks.

If the latter use a calculated field like this --
Expr1: Format([Date open],"y")
This gives you the day of the year.
Then criteria like this --
<IIf(Year([Date open]) Mod 4=0,92,91) Or >IIf(Year([Date open]) Mod
4=0,319,318)

Leap years are evenly divisible by 4. So if not leap year the end of March
is day 90.

This give records from last November year through March this year.
Between DateSerial(Year(Date())-1,11,15) AND DateSerial(Year(Date()),3,31)

Or do you want all records from
15 Nov year1 through 31 Mar year2
15 Nov year2 through 31 Mar year3
15 Nov year3 through 31 Mar year4
etc.?

Thanks Karl. I wasn't clear enough. I don't want the user to input, I want
to take the system's year as the variable. Thus, given a criteria also of
Date()), I will return all future years' records between 11/15 and 3/31.

Can you point the way and 'splain to me?

Thanks again.

:

Between DateSerial([Enter start year (2003)],11,15) AND DateSerial([Enter
end year (2003)],3,31)

:

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

 

[Skunk]

Thanks so much John. This worked! A nice workaround.

This should be simple, right? I can't find or figure how to do it.

How would I set query criteria for selecting records between two dates, with
month and day constant and year variable?

Such as: Between #11/15/??# and #3/31/??# (I used ?? to indicate variable
years.)

Thanks in advance.

If you want to return records for a given range of dates regardless of the
year, fake it out by creating a calculated field in your Query using the
current year:

SearchDate: DateSerial(Year(Date()), Month([datefield]), Day([datefield]))

This will take a table field containing (say) 5/16/1946 and create a new
datefield 5/16/2009. (Don't fear, it will take 2/29/1968 and return 3/1/2009).

You can then use a criterion on this field of

BETWEEN #3/1# AND #5/31#

If you want to "wrap" around the new year you'll need to do it in two pieces -
BETWEEN #11/15# AND #12/31# OR BETWEEN #1/1# AND #3/31#.