QueryHelp on Date

[marklane2001]

Hi

I have a table called TBL_PUPIL and within that it holds the pupils date of birth fldDOB. This is currently formatted DD/MM/YYYY. I want a query to return all pupils whose birthday is in 1 month (30days) or less away and wondered if you could give some advice on how I do this please?

 

[MSAccessRookie]

Hi

I have a table called TBL_PUPIL and within that it holds the pupils date of birth fldDOB. This is currently formatted DD/MM/YYYY. I want a query to return all pupils whose birthday is in 1 month (30days) or less away and wondered if you could give some advice on how I do this please?

Access has a Date() Function that allows you to see Today's Date, and a Month() Function that allows you to see the number of the Month. Put them together and do something like:

SELECT fldFirstName, 
    fldLastName, 
    fldDOB, 
    {Additional student information you need}
FROM TBL_PUPIL 
WHERE Month(fldDOB) = Month(dateadd("m", 1, Date()))
ORDER BY fldDOB, fldLastName, fldFirstName;

 

[marklane2001]

Thanks for the reply. So whenever this query is run it will show me all birthdays that between now and 1 months time?

 

[neileg]

Nope, I think Rookie meant to give you all birthdays in the current calendar month but it's not quite right because DOB is not going to occur in the current year unless the child was only born this year!

So what you need is to take the DOB and extract the day and month and graft on the current year and test this against the current date. So something like (untested) as a calculated field

BirthdayThisYear: DateSerial(year(Date()),Month(fldDOB),Day(fldDOB))

wll produce the current years birthday. Then add a criterion to this field

Between Date() and (Date()+30)

 

[MSAccessRookie]

Access has a Date() Function that allows you to see Today's Date, and a Month() Function that allows you to see the number of the Month. Put them together and do something like:

SELECT fldFirstName, 
    fldLastName, 
    fldDOB, 
    {Additional student information you need}
FROM TBL_PUPIL 
WHERE Month(fldDOB) = Month(dateadd("m", 1, Date()))
ORDER BY fldDOB, fldLastName, fldFirstName;

Why won't this work?

If fldDOB contains #15/10/1991#, then (since the year is not even considered) the result of Month(fldDOB) would be 10.

The result of Month(dateadd("m", 1, Date())) for Date()=#26/09/2008# is also 10.

Am I missing something here?

 

[neileg]

What about the remaining days of September? 27 September 1991 is going to return Month 9. The OP wants a month from now, not next month.

Unless I have misunderstood and that's always possible!

 

[MSAccessRookie]

What about the remaining days of September? 27 September 1991 is going to return Month 9. The OP wants a month from now, not next month.

Unless I have misunderstood and that's always possible!

OK, I see your point. I believe that The OP wants what you are suggesting, so what I said would be incomplete. Thanks for responding.