Age bracket not working

[akhlaq768]

I have created a query to work the age bracket from the Date of Birth to date of visit, both fields are date and time

below are some example of this.

Note that the age bracket of 0-16 isn't a correct result. what is wrong with the query?

Date_Of_Birth DateofVisit Age Bracket
06-Oct-1907 09-May-2008 0 - 16
06-Oct-1907 12-May-2008 0 - 16


Age Bracket:
IIf(([DateofVisit]-([Date_Of_Birth]))/365<='16','0 - 16',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between '17' And '30','17 - 30',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between '31' And '65','31 - 65',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between '66' And '75','66 - 74','75+'))))

 

[pbaldy]

Your calculation is going to return a number, not a string, so you don't want quotes around the test values.

 

[akhlaq768]

so will this work?... in access?

Age Bracket:
IIf[DateofVisit]-[Date_Of_Birth]/365<='16','0 - 16',
IIf[DateofVisit]-[Date_Of_Birth]/365 Between '17' And '30','17 - 30',
IIf[DateofVisit]-[Date_Of_Birth]/365 Between '31' And '65','31 - 65',
IIf[DateofVisit]-[Date_Of_Birth]/365 Between '66' And '75','66 - 74','75+'

 

[boblarson]

Paul said, take away the quotes on the number comparison.

[DateofVisit]-[Date_Of_Birth]/365 is never going to equal text. So, change to

Age Bracket: 
IIf(([DateofVisit]-([Date_Of_Birth]))/365<=16,'0 - 16',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 17 And 30,'17 - 30',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 31 And 65,'31 - 65',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 66 And 75,'66 - 74','75+'))))

 

[pbaldy]

Well no; you didn't drop the quotes but you did drop the required parentheses. I meant this:

<='16'

should be

<=16

 

[pbaldy]

Where would I be without Bob to save me, even though he forgot the parentheses too.

 

[boblarson]

Where would I be without Bob to save me, even though he forgot the parentheses too.

no I didn't, look again < oh darn, the "edit note gave me away" >

 

[boblarson]

Where would I be without Bob to save me

Not save, just attempt to assist to translate by trying other words.

 

[Brianwarnock]

I hope you two gurus will forgive this old guy for butting in , but shouldn't we be telling the Op how to do correct date calculations?
date1-date2 returns the number of days, /365 does not give an accurate number of years around the edges, even though 365.25 would be better it is still not 100% correct, or am I wrong?

Brian

 

[boblarson]

I hope you two gurus will forgive this old guy for butting in , but shouldn't we be telling the Op how to do correct date calculations?
date1-date2 returns the number of days, /365 does not give an accurate number of years around the edges, even though 365.25 would be better it is still not 100% correct, or am I wrong?

Brian

Brian, yes you are correct. Personally I would probably use the Age function that has been posted here frequently (including by RuralGuy) to calculate the age and then set the bucket.

 

[Brianwarnock]

Thanks Bob, it always worries me when I think that I might be disagreeing with experts.

Brian

 

[pbaldy]

I agree Brian. Sometimes I just try to correct what they're doing so they'll understand better (teach them how to fish).

 

[boblarson]

Thanks Bob, it always worries me when I think that I might be disagreeing with experts.

Well, if you are talking about me, I'm no expert. I count myself as a high-intermediate person because when I stand next to Brent Spaulding (datAdrenaline) and Leigh Purvis (LPurvis) THAT is when I am in the company of experts (or at least that is my perception).

 

[pbaldy]

I hope you're not calling me an expert! I'm the MVP that snuck through the back door when nobody was looking. Guys like those Bob mentioned are WAY over my head.

 

[raskew]

Try playing with this, replacing date() with date of visit:

DOB = #4/1/53#
DOV = date()
x =  datediff("yyyy", DOB, DOV) + (DateSerial(year(DOV), month(DOB), day(DOB)) > DOV)
? x
 55 
y = switch(x<=16, "0-16", x<31, "17-30", x<66, "31-65", x<75, "66-74", True, "75+")
? y
31-65

Bob

 

[akhlaq768]

thanks guys,

couldn't do it without your help.

you definately experts in my eyes

 

[akhlaq768]

just a quick question, if my date of birth field is blank, i want it to return a blank field...

how can i modify the query below to show this?

Age Bracket:
IIf(([DateofVisit]-([Date_Of_Birth]))/365<=16,'0 - 16',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 17 And 30,'17 - 30',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 31 And 65,'31 - 65',
IIf(([DateofVisit]-([Date_Of_Birth]))/365 Between 66 And 74,'66 - 74','75+'))))

 

[DCrake]

This is where a function would be better suited. In your query pass both arguments to a function that returns the age band, such as:

Ageband:AgeBracket(Nz([DateofVisit],0),Nz([Date_Of_Birth],0))

Public Function AgeBracket(dtmVisit As Date, dtmDOB As Date) As String

Dim Age As Integer

[COLOR="SeaGreen"]'Is either the visit date or the date of birth empty
'If so return 'Unknown' and exit function[/COLOR]

If dtmVisit = 0 Or dtmDOB = 0 Then
   AgeBracket = "Unknown"
   Exit Function
End If
[COLOR="seagreen"]
'Crudely calculate the age of the person[/COLOR]

Age = Int(dtmVisit-dtmDOB/365.25)


[COLOR="seagreen"]'Determine the age bracket[/COLOR]
Select Case Age
   Case <17 :AgeBracket = "0 - 16"
   Case <31 :AgeBracket = "17 - 30"
   Case <66 :AgeBracket = "31 - 65"
   Case <76 :AgeBracket = "66 - 75"
   Case Else:AgeBracket = "75+"
End Select

End Function

 

[akhlaq768]

i get an error message

Run-time error '6':
Overflow


once i debug the following line is highlighted in yellow

Age = Int(dtmVisit - dtmDOB / 365.25)

 

[Rabbie]

Try writing it as

Age = Int((dtmVisit - dtmDOB) / 365.25)

without the extra brackets the division operation (/) is done before the subtraction (-) which is not what you want.