Age query error if DOB blank (1 Viewer)

wackywoo105 · Apr 15, 2018

I use:

Age: DateDiff("yyyy",[Birthdate],Date())+(Date()<DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])))

to create a column in a query. The problem is I want some instances where Birthdate is blank. In these case it causes an error.

Is there anyway I can just have it be blank for antine there is no birthdate?

isladogs · Apr 15, 2018

Here's an alternative version which doesn't error when the BirthDate is blank:

Code:

DateDiff("yyyy",[BirthDate],Date())+(Format([BirthDate],"mmdd")>Format(Date(),"mmdd"))

Its also simpler

NOTE: My first attempt using the Nz function didn't work so I've removed it!

wackywoo105 · Apr 15, 2018

Thanks. If I use that and then delete a birthdate to test it still gives a data type mismatch error in criterion error.

EDIT I have it set to test if age is over 25 and I think thats whats causing the error. Deleting that clause and it works. I tried "Is Not Null And >25" but it doesn't help.

isladogs · Apr 15, 2018

My first attempt was incorrect & I've just replaced it in post 1
The new version works perfectly for any age

Age1 is the original; Age2 is the new version

wackywoo105 · Apr 15, 2018

Thanks will give that a try shortly.

wackywoo105 · Apr 16, 2018

Just gave it a whirl and works fine. Thanks for your help, it's much appreciated.

Age query error if DOB blank (1 Viewer)

wackywoo105

Registered User.

isladogs

MVP / VIP

wackywoo105

Registered User.

isladogs

MVP / VIP

Attachments

wackywoo105

Registered User.

wackywoo105

Registered User.

Similar threads

Users who are viewing this thread