G
Guest
How do I return the birthdays in the next 30 days in a date of birth field
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
select tbl.dateofbirth from tbl where tbl.dateofbirth <=
now()+30;
now()+30;
J
John Vinson
On Mon, 24 Jan 2005 02:46:23 -0800, "David Oakey" <David
If you have the date of birth in a field named DOB, use a calculated
field in the query:
HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
and use a criterion on it of
BETWEEN Date() AND Date() + 30
John W. Vinson[MVP]
How do I return the birthdays in the next 30 days in a date of birth field
If you have the date of birth in a field named DOB, use a calculated
field in the query:
HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
and use a criterion on it of
BETWEEN Date() AND Date() + 30
John W. Vinson[MVP]
J
John Spencer (MVP)
John,
Does that work if the current date is Dec 20? I am guessing that you would not
get the January birthdates. I don't have a nice simple solution for this. I
think I saw one posted long ago, but I didn't capture the solution.
Does that work if the current date is Dec 20? I am guessing that you would not
get the January birthdates. I don't have a nice simple solution for this. I
think I saw one posted long ago, but I didn't capture the solution.
John said:On Mon, 24 Jan 2005 02:46:23 -0800, "David Oakey" <David
How do I return the birthdays in the next 30 days in a date of birth field
If you have the date of birth in a field named DOB, use a calculated
field in the query:
HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
and use a criterion on it of
BETWEEN Date() AND Date() + 30
John W. Vinson[MVP]
J
John Vinson
John,
Does that work if the current date is Dec 20? I am guessing that you would not
get the January birthdates. I don't have a nice simple solution for this. I
think I saw one posted long ago, but I didn't capture the solution.
Yes, it will work perfectly well. DateSerial() is pretty clever:
?DateSerial(2004, -60, 75)
2/13/1999
In other words, it wraps years, months, days and decades with aplomb.
John W. Vinson[MVP]
G
Guest
Hi JohnJohn Vinson said:Yes, it will work perfectly well. DateSerial() is pretty clever:
?DateSerial(2004, -60, 75)
2/13/1999
In other words, it wraps years, months, days and decades with aplomb.
John W. Vinson[MVP]
When I put the expression in a calculated field, replacint DOB with my
Birthdate field and the criteria in the criteria of the calculated field, I
get a mismatch message in the criteria expression
D
Duane Hookom
As per another thread in another forum, there may be an issue with Null
values in the DOB field. The only other possible issue that I can think of
is the BirthDate field being text.
values in the DOB field. The only other possible issue that I can think of
is the BirthDate field being text.
J
John Spencer (MVP)
Yes I understand that, but try this scenario.
Current Date: Dec 20 2004
Birthdate: Jan 3 1942
HappyHappy: 2004-01-03 is not In Range: 2004-12-20 to 2005-01-19
That is why I think the solution would not work for finding a month's worth of
birthdays. I think you would have to adjust the Happy Happy calculation or the
criteria. Perhaps the criteria should be something along the lines of:
Criteria: Between Date() and Date() + 30 OR Between DateAdd("yyyy",-1,Date())
AND DateAdd("yyyy",-1,Date()) +30
Current Date: Dec 20 2004
Birthdate: Jan 3 1942
HappyHappy: 2004-01-03 is not In Range: 2004-12-20 to 2005-01-19
That is why I think the solution would not work for finding a month's worth of
birthdays. I think you would have to adjust the Happy Happy calculation or the
criteria. Perhaps the criteria should be something along the lines of:
Criteria: Between Date() and Date() + 30 OR Between DateAdd("yyyy",-1,Date())
AND DateAdd("yyyy",-1,Date()) +30
J
John Vinson
Yes I understand that, but try this scenario.
Current Date: Dec 20 2004
Birthdate: Jan 3 1942
HappyHappy: 2004-01-03 is not In Range: 2004-12-20 to 2005-01-19
My mistake, John! Quite right of course.
John W. Vinson[MVP]
G
Guest
Hi Duane, you sorted out the problem in another form, and it works ok with
your code and criteria
your code and criteria