Expression for Birthdays within next 3 months 2

[air0jmb]

I have BirthDate field in tblEmpInfo. I need help with an expression that returns all employees that have a birthday in within 3 months of the first day of current month.

In my query I created a new field <<BirthMonth: =DateSerial(Year(Date()), Month([tblEmpInfo]![Birthdate]),1)>> and in criteria entered >=DateSerial(Year(Date()),Month(Date()),1) and <= DateSerial(Year(Date()), Month(Date())+3,0) but I get a data mismatch msg.

Thanks for any assistance!!

Miek

 

[traingamer]

Might be the first equals sign (=) - that looks like excel formula

BirthMonth: [b]=[/b]DateSerial(Year(Date()), Month([tblEmpInfo]![Birthdate]),1)

BirthMonth: DateSerial(Year(Date()), Month([tblEmpInfo]![Birthdate]),1)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

Seems to work OK for me ... with or without the equal sign.

You should use the DOT operator ... not the BANG(!) operator to designate fields in a table. You may also check that [blue][tblEmpInfo].[Birthdate][/blue] is really a Date/Time field and not just text that looks like a Date.

 

[air0jmb]

Thanks for the responses! However, it's still no working. I simplified the criteria to just >=1/1/06 while dealing with the data type mismatch issue. [GEMS ID] is the Employee ID # and is the PK. Here's the SQL...

SELECT tblEmpInfo.[GEMS ID], DateSerial(Year(Date()),Month([tblEmpInfo].[BirthDate]),1) AS BirthMonth
FROM tblEmpInfo
WHERE (((DateSerial(Year(Date()),Month([tblEmpInfo].[BirthDate]),1))>#1/1/2006#));

traingamer - I dropped the "=".

Golom - I changed "!" to "."; I used the Expression Builder which actually assigns the "!"? And, BirthDate is Date/Time.

If I type >=1/1/06 into criteria, Access changes it to >=1/1/6, so I've entered #'s before and after. Still no luck. When I run query, I still get Data Type Mismatch msg.

Mike

 

[traingamer]

This works for me:

...where DateSerial(Year(Date()),Month([BirthDate]),1) between DateSerial(Year(Date()),Month(Date()),1) and  DateSerial(Year(Date()),Month(Date())+3,1)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[air0jmb]

Hmmm... If I use > And <, I get data type mismatch. If I use Between, I don't.

Also, some records don't have values in BirthDate field. If I enter Is Not Null as criteria for BirthDate, it works. (using Between in BirthMonth criteria).

I'd like to understand 1) Why > And < is causing me data type mismatch? And, 2) Why null records is causing data type mismatch?

Thanks for helping!

Mike

 

[traingamer]

1) Data type 'date' is different from 'number'

2) because null (variant) is not a valid date type

Try using the nz function to default to 1 (January) if null value encountered:

...where DateSerial(Year(Date()),Month([COLOR=red]nz([BirthDate],1)[/color]),1) between DateSerial(Year(Date()),Month(Date()),1) and  DateSerial(Year(Date()),Month(Date())+3,1)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

I would personally stick with the "IS NOT NULL" filter.

After all, it doesn't make much sense to compute if someone's birthday is in the next three months if you don't know when the birthday is.

traingamer

This

Month(nz([BirthDate],1))

Doesn't convert to "January". It is equivalent to

Month(#12/31/1899#)

Which will return 12 (i.e. December) if [BirthDate] is NULL.

 

[traingamer]

Quite right, Golom. (None of my tests included a blank date - bad, bad tester.) :-(
You could substitute

nz([BirthDate],"1/1/2006")

for

nz([BirthDate],1)

if you wanted to use January as the default for 'no birthday', but 'is not null' makes much more sense.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[air0jmb]

Thanks to both of you!

Mike