Query to return all the birthdays for next month.

[JTKrol]

I came up with the following string to do this but I keep getting an error...

Next_months_Birthday: DateSerial(Year(Date())+IIf(Format(Date()),"mmdd") >Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day])

The error I get is ....The expression you entered contains invalid syntax, or you need to enclose your text data in quotes. What am I missing here? I attached a screen shot hoping that will help.

Thank you!!!
Joe

[June7]

Try:

SELECT * FROM Employee_Info_Table WHERE Format(DOB, "YYYYMM") = Format(DateAdd("m", 1, Date()), "YYYYMM");

[Mahendra1000]

Next_months_Birthday: DateSerial(Year(Date())+IIf(Format(Date()),"mmdd") >Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day])

There's an extra open brackets ")" in your code. Have a look into it too.

[JTKrol]

There's an extra open brackets ")" in your code. Have a look into it too.

I took out the extra ) I removed the one after the 1,0 but now the error states that I have and Expression that contains the wrong number of arguments.

[JTKrol]

Try:

SELECT * FROM Employee_Info_Table WHERE Format(DOB, "YYYYMM") = Format(DateAdd("m", 1, Date()), "YYYYMM");

Will this work in December for January? My understanding was that by placing a 1 in the calculation you are essentially adding + 1 to a numerical date month which works fine until you get to December when it will add 12 + 1 returning 13 which will not give you January dates in December. Or was that something from an older version of Access that they fixed?

Thanks
Joe

[JTKrol]

Try:

SELECT * FROM Employee_Info_Table WHERE Format(DOB, "YYYYMM") = Format(DateAdd("m", 1, Date()), "YYYYMM");

I've tried putting that in but I'm getting syntax errors.

[JTKrol]

June7,

I cleared up all the syntax problems but it is not working at all it runs the Query but does not return any values...

Thank you for Trying to help.
Joe

[JTKrol]

There's an extra open brackets ")" in your code. Have a look into it too.

What I am really trying to do is to use the system clock on the PC to have Access look at the current month and then look forward a month at the DOB field and pull out all the birthdays that have the same month as next month.

[June7]

My query should accomplish that. It works for me. Will work for December. Add 1 month returns January of next year.

Are you sure there are records with DOB that meet the criteria?

Post your exact attempted query.

Provide sample data.