Question Date / Month Only

[Khartoum]

Hi, I want to record dates of birth but without the year of birth so I can run a weekly query / report of birthdays in a specific week in order to send them an electronic greeting. Is this possible?
thanks

 

[bob fitz]

What you want is possible. One way would be to have a field for the day part of the DOB and another field for the month but I would store the DOB in a date field and use the Day() and Month functions in a query to retrieve just the data that you require. The full DOB would be just as easy to enter and you would also know the exact age of each person.

 

[Khartoum]

Bob, what i have done in the query is extract the day and month so i have e.g. 25 jul but now in the query how would i get the critieria to look at the dd/mmm and see that it equals Date()

 

[bob fitz]

Can you show us the SQL of your quiery.

 

[Khartoum]

Here is the current SQL now i am just after a way to match the dd mmm to the current date (maybe datepart?

SELECT tblClients.ClientName, tblClients.Address1, tblClients.PostCode, tblClients.ContactNumber, tblClients.Email, tblClients.Mobile, tblClients.Birthday, Format([tblclients].[birthday],'dd mmm') AS BirthdayCard
FROM tblClients
GROUP BY tblClients.ClientName, tblClients.Address1, tblClients.PostCode, tblClients.ContactNumber, tblClients.Email, tblClients.Mobile, tblClients.Birthday, Format([tblclients].[birthday],'dd mmm');

 

[bob fitz]

Try
Format(Date(),'dd mmm')
as the criteria in the BirthdayCard column

 

[Khartoum]

Brilliant Bob, many thanks!

 

[Khartoum]

Bob, just as a thought, is there a way I could make the criteria so it gave me the birthdays for the coming 7 days

 

[bob fitz]

Bob, just as a thought, is there a way I could make the criteria so it gave me the birthdays for the coming 7 days

This seems to work. Let us know if it works for you.
Between Format(Date(),'dd mmm') And DateAdd("d",+7,Format(Date(),'dd mmm'))

 

[Pat Hartman]

Between Format(Date(),'dd mmm') And DateAdd("d",+7,Format(Date(),'dd mmm'))

Formatting turns a date into a string. Once a date is a string, it works like a string so this code will not produce the correct results. It is one thing to do an = compare with dates formated as strings and quite another to do a range compare. The range compare must be done on either date fields or strings formatted to accommodate the compare.

DateSerial(Year(Date()), Month(DOB), Day(DOB)) Between Date() And DateAdd("d",+7,Date())

Feb 29 will become Mar 01 in non-leap years with this method. You could fix that with a more complicated expression but you'll need to decide how you want to handle this situation.

 

[Khartoum]

Hi Chaps, Bob, yours brought up every date including and after;I got a data mismatch from yours Pat but was it that i had to transpose information into that dateserial line? Here is my current sql:

SELECT tblClients.ClientName, tblClients.Address1, tblClients.PostCode, tblClients.ContactNumber, tblClients.Email, tblClients.Mobile, tblClients.Birthday
FROM tblClients
WHERE (((Format([tblclients].[birthday],'dd mmm'))=Format(Date(),'dd mmm')))
GROUP BY tblClients.ClientName, tblClients.Address1, tblClients.PostCode, tblClients.ContactNumber, tblClients.Email, tblClients.Mobile, tblClients.Birthday;

thanks for time chaps