Query a birthdate

[Valery]

Hi all,

I have a query - here is the coding:

[COLOR=#1f497d][FONT=Calibri][SIZE=3][FONT=Calibri]SELECT Patients.BirthDate, [FirstName] & " " & [LastName] AS FullName, Patients.Email[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri][SIZE=3][FONT=Calibri]FROM Patients[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri][SIZE=3][FONT=Calibri]WHERE (((Patients.BirthDate)=[B]?????need coding here?????[/B]) AND ((Patients.Email) Is Not Null) AND ((Patients.Deactivate_Ind)="N"));[/FONT][/SIZE][/FONT][/COLOR]

I would like it to return, on my daily to do list form, the clients whose birthday = today's date

The date field property is Date/Time with the format set at dd mmm yyyy in the table's field property.

I would like to keep my Regional Settings as they are, that is:
Short Date: dd-MMM-yy
Long Date: dddd, MMMM dd, yyyy

I have tried several syntax in the query (where the question marks are right now) with a fake record containing a birthdate set at today's date but in 1962... it returns no records...
FYI: as a test, put in #22-Feb-62#, and it did show that one record.

Thank you for your help!

 

[pbaldy]

Use the Month() and Day() functions on both your field and the Date() function.

 

[Valery]

Thank you Paul but if I knew how to do that, I would have done it.

Can anyone just provide the missing coding where the question marks are located? THANK YOU - much appreciated.

 

[Brianwarnock]

Try

Where Dateserial(Year(Date()), Month(Birthdate), Day(Birthdate)) = Date()

Brian

 

[Valery]

It is returning Data Type Mismatch in criteria expression. Awww it is so close... do I need to define the day and month?

 

[Brianwarnock]

Provided your Birthdate is a date type field it should work as given.

Brian

 

[Valery]

Ok, made it work this way - thanks to you - had no idea how to code... May not be the best way but it works.

DayNo: DatePart("d",[BirthDate]) with Criteria set to: DatePart("d",Now())
MonthNo: DatePart("m",[BirthDate]) with Criteria set to: DatePart("m",Now())

What do you think?

 

[Brianwarnock]

Firstly there is no need to use Now() when you do not require the time use Date() , this is similar to the approach Paul was suggesting except that he would have used Month(Birthdate) = month(Date()) and Day(Birthdate) = Day(Date())

Brian

 

[Valery]

Will follow your advice! Thank you!