Age group Query.

digitalxni · Apr 13, 2010

Hey guys, I've got a module that reads a date of birth and puts it into the relevant age group which works fine except that I want to ignore all people over the age of 4 which is so simple yet I can't seem to do it!

Here is my function:

Code:

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"
End Select
End Function

I tried using an If statement but that didn't seem to work

Any ideas?

Brianwarnock · Apr 13, 2010

What do you mean by ignore, at the moment you will return anothing what did you want? If it is a positive something like "N/A" then use Case Else which is correct coding.

BTW why use Now() rather than Date() when you have no interest in the time.

Brian

digitalxni · Apr 13, 2010

At the moment i'm getting this in the query:

CountofDob | Age Group

1
2 Under 1
1 1 Year

So it is counting the number of people who are not in an age group which is what I don't want to know or show. I understand about using the Case Else but what code would I use to ignore those people?

Brianwarnock · Apr 13, 2010

That is nothing to do with your function but the fact that you are not filtering in the query, if you used case else and put "N/A" for example then in the query you could exclude all "N/A", probably at the moment it is ="" but might be Is Null

Brian

digitalxni · Apr 13, 2010

Worked perfectly! Thanks! Last thing to sort with this query: I want to make sure that if no child falls into a group (for instance the under 1 group) I still want to show that group and say that there are 0 people in it. In another thread someone said about using the following: iif([Age Group]Value=0,0,[Age Group]). But I have no idea what this does or where I would even put it!

Brianwarnock · Apr 13, 2010

I have no idea what

iif([Age Group]Value=0,0,[Age Group]).

is all about.

If no child falls into an age group then you will have no data to count.
When I have done age grouping I have not used Count but have produced all of the results in 1 row not a row per group. I then have used sum if approach

Sum(iif(agegroup="criteria",1,0)
so your query would have said something like
countGroupA:Sum(iif(agegroup(dob)="A) Under 1",1,0) etc for other groups.

If you want to pursue the current method I think that you will need atable with the groups defined in and do a leftjoin to enable all of the groups to be selected and use NZ for when there are no values in the query.

Brian

digitalxni · Apr 13, 2010

I'm now trying this function I pasted before on another similar database. The syntax is the same as is the SQL in the query yet I'm getting a data type mismatch in the expression error! I'm using the exact syntax for the expression and the exact SQL query and both fields in both databases are the date/time type.

digitalxni · Apr 13, 2010

Right so the problem is because I have some children where I don't have their date of births. I tried adding a 'is not null' on the count of DoB but that isn't working :/

Brianwarnock · Apr 14, 2010

I assume that you are getting #Error returned from your function?

You need to check in the function change the start to

Public Function AgeGroup(DOB As Variant) As String
Dim intAge As Integer
If Not Isdate(DOB) then exit function

Or you might want to return some string which can be filtered out in a query

If Not IsDate(DOB) Then
AgeGroup = "Invalid Dob"
Exit Function
End If

The change of the declaration of DOB from Date to Variant allows incorrect dates to be handled by your code.

Brian

Age group Query.

digitalxni

Registered User.

Brianwarnock

Retired

digitalxni

Registered User.

Brianwarnock

Retired

digitalxni

Registered User.

Brianwarnock

Retired

digitalxni

Registered User.

digitalxni

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread