Dates

[El Jagang]

I've posted a similar question in "Reports", however I was thinking that perhaps I could create a query that would pull the information I need.

I have a database that lists every employee's birthday within my directorate. I'd like this query to only pull the employee records of those whose birthday is coming up in the next 30 days.

I'm not sure what formula I should use. At the moment, there are only three employees who fit this criteria, but I don't know how to make a formula ignore the year; one such employee was born on 2-Jan-57. I can't use a simple DateAdd, because it also takes into account the year.

Any help would be greatly appreciated.

 

[The_Doc_Man]

I guess it depends on just how bizarre you want to get...

If the dates are stored as true date/time fields (as opposed to text fields) then this isn't TOO difficult. (Take this with a grain of salt...)

You could compute this as a function based on the Julian date. The Julian date separates the year from the day-of-year. You would basically do this: pull the year out of the date and generate a date of 1-jan-{year}. Then convert that date to a DOUBLE (using CDBL function). Then convert the input date to a DOUBLE as well. Subtract the beginning-of-year from the current date and then add 1 to get the Julian equivalent of the input date.

OK, with that function in hand, your query simply becomes to convert each birthday to Julian date, then convert the start of the month and the end of the month to Julian, then find all Julian birth dates between the starting and ending Julian dates for the month.

You might wish to search this forum for ways to compute the Julian date. I vaguely recall seeing it before.