Displaying a date of birth

[datamismatch]

I am using Access 97.

I have a table of a list of members each showing the date of birth for each one (DOB).

I want to send a greeting to each on their birthday using a form and I need to develop a query showing the day and month for each one.

How can I do this please?

 

[dcx693]

I need to develop a query showing the day and month for each one.

So what exactly are you looking for? A way to get the month from the date field? Use the Month() function to get the numeric number. If you want the month name, then use the Format([dob],"mmmm") function on the date field to get the month name. To get the day, use the Day() function.

 

[datamismatch]

Thanks, I need to get both the day and month together, the DOB field in the table is in the form 21/06/1996!

 

[dcx693]

What format do you want the displayed date and month to be in? How do you want it to look?

 

[datamismatch]

I must be dense

the format 31 May would be fine
Thanks for your help

 

[dcx693]

Try Format([DOB],"00") & Format([DOB],"mmmm")

That expression will create a two-digit date, so 1 May will display as 01 May. You can change the "00" to "0" if you'd rather not have that.

 

[datamismatch]

thanks for your help,
two things 1. when I run the query I get a "data type mismatch in criteria expression", and
2. can I ask for all the records on a given date?
Thanks

 

[dcx693]

What exactly is your criteria expression? The expression:
Format([DOB],"00") & Format([DOB],"mmmm")
is not a critieria, it should be put into the "Field:" line of a blank column.

If you want all records on a given date, use your DOB field and type in a date in the criteria line.

 

[datamismatch]

Thanks again, I'm not explaining this very well...
1. I was using your suggestion as a criteria in the DOB column as you correctly guessed. I have now created it in a column of its own as you suggested and part of it is working - for example the display I now get for some whose DOB is 29/3/1925 displays as "9220March". I still have something wrong.

2. I want to be able to search and display a date and month in any year. Is there a wild card I can include with the criteria to stop the year defaulting to 2004. I'm trying to create a query that will give me a list of birthdays for each day.

Hope that makes it easier for you to understand.

 

[dcx693]

For your criteria in the DOB column, you can use these two statements:
Month([DOB])=9
Day([DOB])=25

That's if you want to search for September 25th of any year. If you want to have Access prompt you for those numbers when you run the query, have something like this:
Month([DOB])=[Please enter the month]
Day([DOB])=[Please enter the day]

You can combine those two query statements onto one line also, if you want:
Month([DOB])=9 And Day([DOB])=25

 

[datamismatch]

displaying a date of birth

Thank you so very much, that was exactly what I was looking for and the query is working well.
Thanks again for all your tolerance and help.