Upcoming Birthday Query - Avoid #Error?

[ersocia]

DateSerial Function - Avoid #Error?

in one of the databases i'm rebuilding, i'm also creating (from scratch) some queries and reports that need to exist, but never did in the old version of the database. one of these is a query that calculates a resident's next birthday date based on their actual birthdate. here's where the problem lies...

DateSerial(Year(Date())+IIf(Month(Resident_Info.DOB_1)*100+Day(Resident_Info.DOB_1)<=Month(Date())*100+Day(Date()),1,0),Month(Resident_Info.DOB_1),Day(Resident_Info.DOB_1)) AS Birthday_1

(i'm aware that this may not be the best or most efficiently written statement. i'm still pretty new to SQL View )

i would like to edit this statement so that if, by chance, the field DOB_1 is empty/null, it will ignore that record and move on.. so that i don't end up with a ton of #Error values down the "Birthday_1" column.

i tried experimenting with NZ(), but couldn't quite figure out how to get it to work. any ideas?

 

[jzwp22]

How about something like this:

IIF(IsNull(Resident_Info.DO B_1),NULL,DateSerial(Year(Date())+IIf(Month(Resident_Info.DO B_1)*100+Day(Resident_Info.DOB_1)<=Month(Date())*1 00+Day(Date()),1,0),Month(Resident_Info.DOB_1),Day (Resident_Info.DOB_1)))

 

[ersocia]

How about something like this:

IIF(IsNull(Resident_Info.DO B_1),NULL,DateSerial(Year(Date())+IIf(Month(Resident_Info.DO B_1)*100+Day(Resident_Info.DOB_1)<=Month(Date())*1 00+Day(Date()),1,0),Month(Resident_Info.DOB_1),Day (Resident_Info.DOB_1)))

well, i must say, i didn't expect such a simple and straightforward solution that i completely overlooked. thanks so much for pointing out what i should have figured out for myself in the first place i feel a bit :banghead:

thanks again

 

[jzwp22]

You're welcome! (Don't beat yourself up; we've all been in your position before).