Birthdays within five days

[jdn]

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

 

[Clifford Bass]

Hi John,

Try DateSerial(Year(Date()),Month([birthday]),Day([birthday]))-Date()
Between -5 And 5.

Clifford Bass

 

[Clifford Bass]

Hi John (2),

Unfortunately that will not provide what John (1) needs. It will only
provide for babies who were born within the last five days and are about to
be born within the next five days .

Clifford Bass

 

[Clifford Bass]

Oops, have to rethink that. It will not work in the few days before the end
of the year and the few days after the start of the year.

Clifford Bass

Hi John,

Try DateSerial(Year(Date()),Month([birthday]),Day([birthday]))-Date()
Between -5 And 5.

Clifford Bass

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

 

[fredg]

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

Add a new Column to the query grid.
BirthdaysThisMonth:Format([Birthday],"mm/dd")

As criteria on this column, write:

Between Format(Date()-5,"mm/dd") and Format(Date()+5,"mm/dd")

 

[Clifford Bass]

Hi John,

Okay, try this one:

DateSerial(Year(Date()),Month([birthday]),Day([birthday]))-Date() Between -5
And 5 OR
DateSerial(Year(Date())+IIf(Month(Date())=1,-1,1),Month([birthday]),Day([birthday]))-Date() Between -5 And 5

Clifford Bass

 

[John W. Vinson]

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

Well, you've gotten several answers (some of which will work <g>) but here's
my recommendation: put a calculated field in your query by typing

HappyHappy: DateSerial(Year(Date()), Month([birthday]), Day([birthday]))

This will contain this year's birthday anniversary (March 1 in a non-leap year
if the birthdate is February 29). You can use a criterion of

BETWEEN Date()-5 AND Date()+5

or any other reasonable date/time criterion.

 

[Duane Hookom]

Actually, I think you need to account for end of the year birthdays.

BirthdaysThisMonthateSerial(Year(Date()),Month([Birthday]),Day([Birthday]))

And set the criteria to:
Between Date()-5 and Date()+5

--
Duane Hookom
Microsoft Access MVP

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

Add a new Column to the query grid.
BirthdaysThisMonth:Format([Birthday],"mm/dd")

As criteria on this column, write:

Between Format(Date()-5,"mm/dd") and Format(Date()+5,"mm/dd")

 

[Clifford Bass]

Hi John,

Here is a somewhat more elegant solution that should work any time of
the year:

DateSerial(Year(Date()-5),Month([birthday]),Day([birthday]))-Date() Between
-5
And 5 OR
DateSerial(Year(Date()+5),Month([birthday]),Day([birthday]))-Date() Between
-5 And 5

Clifford Bass

 

[jdn]

Umm. I get a data type mismatch in criteria message when I use this as the
criteria and with the Happy...... as the field. What is happening?

john

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

Well, you've gotten several answers (some of which will work <g>) but here's
my recommendation: put a calculated field in your query by typing

HappyHappy: DateSerial(Year(Date()), Month([birthday]), Day([birthday]))

This will contain this year's birthday anniversary (March 1 in a non-leap year
if the birthdate is February 29). You can use a criterion of

BETWEEN Date()-5 AND Date()+5

or any other reasonable date/time criterion.

 

[jdn]

I get a data type mismatch in criteria message when I use this as the
criteria and with the Happy...... as the field. What is happening?

--
john

Actually, I think you need to account for end of the year birthdays.

BirthdaysThisMonthateSerial(Year(Date()),Month([Birthday]),Day([Birthday]))

And set the criteria to:
Between Date()-5 and Date()+5

--
Duane Hookom
Microsoft Access MVP

I want to find all birthdays that fall within five days of today. (the
birthday field is a date with month day and year)
I started to use Month([birthday])=Month(Date()) And Day([birthday])
Between Day(Date())-5 And Day(Date())+5 which would work except for
dealing with last month's bdays and next months. I don't know how to address
this.

Add a new Column to the query grid.
BirthdaysThisMonth:Format([Birthday],"mm/dd")

As criteria on this column, write:

Between Format(Date()-5,"mm/dd") and Format(Date()+5,"mm/dd")

 

[John W. Vinson]

Umm. I get a data type mismatch in criteria message when I use this as the
criteria and with the Happy...... as the field. What is happening?

Please open your query in SQL view and post the SQL text here. It should work,
there must be some other problem.

 

[Lord Kelvan]

my money would be on that his birthday field is text not a date/time