Age Group function doesn't like null fields

[digitalxni]

Hey guys,

I'm running the following function from a query:

Public Function AgeGroup(DoB As Date) As String
Dim intAge As Integer
intAge = DateDiff("yyyy", [DoB], Now()) + Int(Format(Now(), "mmdd") < Format([DoB], "mmdd"))
Select Case intAge
Case Is < 1
AgeGroup = "A) Under 1"
Case 1
AgeGroup = "B) 1 Year"
Case 2
AgeGroup = "C) 2 Years"
Case 3
AgeGroup = "D) 3 Years"
Case 4
AgeGroup = "E) 4 Years"
Case Else
AgeGroup = "N/A"
End Select
End Function

This works fine except that now I have some empty DoB fields and this is causing a data mismatch in the expression. I've tried using 'is not null' in the query and a If is not null in the function but none of this is working. Does anyone have any ideas?

 

[pbaldy]

This will not allow a Null:

DoB As Date

You can change that to a variant and test for Null before the DateDiff function, which I think would also barf on the Null.

 

[digitalxni]

I changed to this:

Public Function AgeGroup(DoB As Date) As Date
Dim intAge As Integer
If DoB Is Not Null Then intAge = DateDiff("yyyy", [DoB], Now()) + Int(Format(Now(), "mmdd") < Format([DoB], "mmdd"))

And this gives me a compile error and type mismatch on the DoB after the If.

 

[pbaldy]

For starters, I meant this line:

Public Function AgeGroup(DoB As Date) As String

would have to be

Public Function AgeGroup(DoB As Variant) As String

The Date data type can not accept a Null. Then before the line setting intAge I'd have:

If IsNull(DoB) Then
  AgeGroup = "N/A"
  Exit Function
End If

 

[digitalxni]

Cool looks good! Now I'm getting a Type mismatch error highlighting:

AgeGroup = "D) 3 Years"

I've just had a scan down the DoB entries and I don't see anything that could cause an error :s

 

[pbaldy]

Did you change the last bit back to String?

Public Function AgeGroup(DoB As Variant) As String

You changed it to Date in the last post.

 

[digitalxni]

Just did and it works perfectly! Thanks for all the help

 

[pbaldy]

No problemo, glad we got it sorted out.

 

[digitalxni]

I know this is the Function sub-forum but do you have any idea why when adding the query to a report and trying to display it it keeps opening prompt boxes for every field in the Child table? I'm only selecting DoB from Child in the query.

 

[digitalxni]

Never mind, sorted that report issue, it was trying to order by fields I hadn't even put on the report! Just realised I forgot to add something to the end of the query/function to add up the totals of the groups. can't seem to work out how to do that though. All I can think of is running another query to count all the children if their age is less than 5.

 

[pbaldy]

Adding them up would more commonly be done on a form or report. You could also do a totals query to sum them up.

 

[digitalxni]

Managed to do it in the report. In the detail section I had a text box which did a running total then in the report footer I used another textbox which grabbed the total from the other one and it works great

 

[Brianwarnock]

What is bloody annoying about this thread is that it is really a continuation of another thread that I have just wasted my time responding to.

Brian