IIF including OR within query

[rodmc]

Hi folks

Having a small problem with IIF.

I have a query that calculates a persons age using datediff, Im then required to put these into age bands. So i thought I'd use and IIF statement to check the calculated field and then for it to specify which age band it is in.

Problem is as I see it, how do I put in the OR clause into the expression?

What I was thinking was something like this:

ageband: IIf([AGE]<25,"0-25")

but I also want to add the other age bands into this like

ageband: IIf([AGE]<25,"0-25" Or [AGE] Between 25 And 30,"25-30")

clearly my syntax is wrong cos Im getting strange results, any pointers would be greatly appreciated

Cheers

Rodmc

PS forget the fact Ive used BETWEEN I know this is not inclusive of the numbers stated

 

[DCrake]

Create a functions to determine the age band

Public Function GetAgeBand(AnyAge As Integer) As String

Select case AnyAge
    Case <= 25 :GetAgeBand = "0-25"
    Case <= 30 :GetAgeBand = "26-30"
    Case <= 40 :GetAgeBand = "31-40"
    Case Etc
End Select

End Function

Usage in query

AgeBand:GetAgeBand([Age])

However you age should be calculated in the first place and not stored in a field as the age changes daily.

 

[rodmc]

Create a functions to determine the age band

Public Function GetAgeBand(AnyAge As Integer) As String

Select case AnyAge
    Case <= 25 :GetAgeBand = "0-25"
    Case <= 30 :GetAgeBand = "26-30"
    Case <= 40 :GetAgeBand = "31-40"
    Case Etc
End Select

End Function

Usage in query

AgeBand:GetAgeBand([Age])

However you age should be calculated in the first place and not stored in a field as the age changes daily.

Cheers bud, fantastic stuff!