Dates problem

[Mick]

I have an Access database table with a field in it for "birthdays" with a
format of dd/mm/yyyy. There are two "birthdays" fields in each record (Mr &
Mrs) I would like to write an Access Query to only select those people with
a birthday coming up in say the next month. So in the end th equery shows say
"names" and "birthdays" for either of the two people in each record having a
birthday in the forthcoming
month.
I think I need to compare the dd/mm (of each of the birthdys) against the
dd/mm of this year, ignoring the yyyy
Thanks for your help and interest Mick

 

[Allen Browne]

In query design view, enter an expression like this into a fresh column in
the *Field* row:
Month([HisBirthday])
Change "HisBirthday" to your actual field name.
In the Criteria row under this field, enter the month number, e.g.:
7

Repeat this for:
Month([HerBirthday])
Be sure to put the criteria on the next line in query design (the Or, so you
get his or hers.)

If you need something more flexible, you can create this field:
HisThisYear: DateSerial(Year(Date()), Month([HisBirthday]),
Day([HisBirthday]))
That expression will show the date of his birthday this year. As it is a
date/time field, you can use any criteria such as:
Between #7/7/2006# And #8/8/2006#

Since you use d/m/y dates, this article might also be worthwhile:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Mick]

Allen, many thanks, kind regards Mick

In query design view, enter an expression like this into a fresh column in
the *Field* row:
Month([HisBirthday])
Change "HisBirthday" to your actual field name.
In the Criteria row under this field, enter the month number, e.g.:
7

Repeat this for:
Month([HerBirthday])
Be sure to put the criteria on the next line in query design (the Or, so you
get his or hers.)

If you need something more flexible, you can create this field:
HisThisYear: DateSerial(Year(Date()), Month([HisBirthday]),
Day([HisBirthday]))
That expression will show the date of his birthday this year. As it is a
date/time field, you can use any criteria such as:
Between #7/7/2006# And #8/8/2006#

Since you use d/m/y dates, this article might also be worthwhile:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access database table with a field in it for "birthdays" with a
format of dd/mm/yyyy. There are two "birthdays" fields in each record (Mr
&
Mrs) I would like to write an Access Query to only select those people
with
a birthday coming up in say the next month. So in the end th equery shows
say
"names" and "birthdays" for either of the two people in each record having
a
birthday in the forthcoming
month.
I think I need to compare the dd/mm (of each of the birthdys) against the
dd/mm of this year, ignoring the yyyy
Thanks for your help and interest Mick

 

[Frank]

Let's say your table is called tblPeople and it has these fields:
strHisName
dtmHisDoB
strHerName
dtmHerDoB

First, create and save a query called qryComingBirthdays_0 with this SQL:
SELECT strHisName AS strName, Day(dtmHisDoB) AS d, Month(dtmHisDoB) AS m
FROM tblPeople
UNION ALL SELECT strHerName AS n, Day(dtmHerDoB) AS d, Month(dtmHerDoB) AS m
FROM tblPeople;

Next, create and save a query called qryComingBirthdays with this SQL:
SELECT strName, IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) &
"/" & CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))) AS dtmDate
FROM qryComingBirthdays_0
WHERE (((IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))))<Now()+30));

qryComingBirthdays will give you the names and birthday dates over the next
30 days.

Please let me know how you go with this at
(e-mail address removed)

 

[Frank]

On reflection, SQL for qryComingBirthdays would be better as:
SELECT qryComingBirthdays_0.strName,
IIf([m]<Month(Now()),DateSerial(Year(Now())+1,[m],[d]),DateSerial(Year(Now()),[m],[d])) AS dtmDate
FROM qryComingBirthdays_0
WHERE
(((IIf([m]<Month(Now()),DateSerial(Year(Now())+1,[m],[d]),DateSerial(Year(Now()),[m],[d]))) Between Now() And Now()+30));

Let's say your table is called tblPeople and it has these fields:
strHisName
dtmHisDoB
strHerName
dtmHerDoB

First, create and save a query called qryComingBirthdays_0 with this SQL:
SELECT strHisName AS strName, Day(dtmHisDoB) AS d, Month(dtmHisDoB) AS m
FROM tblPeople
UNION ALL SELECT strHerName AS n, Day(dtmHerDoB) AS d, Month(dtmHerDoB) AS m
FROM tblPeople;

Next, create and save a query called qryComingBirthdays with this SQL:
SELECT strName, IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) &
"/" & CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))) AS dtmDate
FROM qryComingBirthdays_0
WHERE (((IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))))<Now()+30));

qryComingBirthdays will give you the names and birthday dates over the next
30 days.

Please let me know how you go with this at
(e-mail address removed)

I have an Access database table with a field in it for "birthdays" with a
format of dd/mm/yyyy. There are two "birthdays" fields in each record (Mr &
Mrs) I would like to write an Access Query to only select those people with
a birthday coming up in say the next month. So in the end th equery shows say
"names" and "birthdays" for either of the two people in each record having a
birthday in the forthcoming
month.
I think I need to compare the dd/mm (of each of the birthdys) against the
dd/mm of this year, ignoring the yyyy
Thanks for your help and interest Mick

 

[Mick]

Thaks Frank, I will have a go, and let you know Cheers Mick

On reflection, SQL for qryComingBirthdays would be better as:
SELECT qryComingBirthdays_0.strName,
IIf([m]<Month(Now()),DateSerial(Year(Now())+1,[m],[d]),DateSerial(Year(Now()),[m],[d])) AS dtmDate
FROM qryComingBirthdays_0
WHERE
(((IIf([m]<Month(Now()),DateSerial(Year(Now())+1,[m],[d]),DateSerial(Year(Now()),[m],[d]))) Between Now() And Now()+30));

Let's say your table is called tblPeople and it has these fields:
strHisName
dtmHisDoB
strHerName
dtmHerDoB

First, create and save a query called qryComingBirthdays_0 with this SQL:
SELECT strHisName AS strName, Day(dtmHisDoB) AS d, Month(dtmHisDoB) AS m
FROM tblPeople
UNION ALL SELECT strHerName AS n, Day(dtmHerDoB) AS d, Month(dtmHerDoB) AS m
FROM tblPeople;

Next, create and save a query called qryComingBirthdays with this SQL:
SELECT strName, IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) &
"/" & CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))) AS dtmDate
FROM qryComingBirthdays_0
WHERE (((IIf([m]<Month(Now()),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())+1)),CDate(CStr([d]) & "/" & CStr([m]) & "/" &
CStr(Year(Now())))))<Now()+30));

qryComingBirthdays will give you the names and birthday dates over the next
30 days.

Please let me know how you go with this at
(e-mail address removed)

I have an Access database table with a field in it for "birthdays" with a
format of dd/mm/yyyy. There are two "birthdays" fields in each record (Mr &
Mrs) I would like to write an Access Query to only select those people with
a birthday coming up in say the next month. So in the end th equery shows say
"names" and "birthdays" for either of the two people in each record having a
birthday in the forthcoming
month.
I think I need to compare the dd/mm (of each of the birthdys) against the
dd/mm of this year, ignoring the yyyy
Thanks for your help and interest Mick