Checking for Birthdays using D.O.B.

[gr8R]

I have a table with the usual details about customers. I want a query to check to see if there are any customer birthdays within 5 days of todays date.

I put the following code into the Date of Birth field criteria:
Between DatePart("m",Date()) And DatePart("m",DateAdd("d",5,Date())) And Between DatePart("d",Date()) And DatePart("d",DateAdd("d",5,Date()))

However it did not return any results when it should have. Anyone know whats wrong with my criteria or how else i could do this?

Also, i am trying to figure out of way of making the query not show itself if it finds no records that match its criteria. I tried using a macro with the following code:

OpenQuery (Birthday Check Query)
Close Condition column: Count([Customer ID])=0

I have tried many variations (Dcount, Sum, Dsum) but it comes up with the error: "Can't find name 'Customer ID' you entered in the expression"

Any help will be appreciated

 

[Aivars]

Hi!
I think following is more simple.

[Date of Birth]>=date() and [Date of Birth]<=date()+5
or
[Date of Birth] Between date() And date()+5

Aivars

 

[gr8R]

I originally had:
Between date() And date()+5

but there is a big problem with this. It takes account of the year. I want it to select dates of birth that have the same month and day (not year) because birthdays happen every year, not just this year.

I need an expression that will match the day and month of the date of birth and not the year.

 

[nomus1]

I accomplished this by formatting the birthdate and putting my selection criteria on the formatted column like so:

SELECT Employee.LastName, Employee.FirstName, Employee.BirthDate, Format([birthdate],&quot;mmdd&quot AS BDate
FROM Employee WHERE (((Format([birthdate],&quot;mmdd&quot)>Format(Date(),&quot;mmdd&quot And (Format([birthdate],&quot;mmdd&quot)<=Format(DateAdd(&quot;y&quot;,Date(),10),&quot;mmdd&quot));

Hope this helps...

 

[JerryDennison]

Try this:

DateSerial(Year(Date()), Month(DOB), Day(DOB)) Between Date() and DateAdd(&quot;d&quot;, 5, Date())

 

[Aivars]

?date()+100
03/21/2002