What is wrong with this function?

[bentley1]

Age Group: Switch([Attained Age]<='50','<50',[Attained Age]>'50' And [Attained Age]<='55','51-55',[Attained Age]>'55' And [Attained 

Age]<='60','56-60',[Attained Age]>'60' And [Attained Age]<='65','61-65',[Attained Age]>'65' And [Attained Age]<='70','66-70',[Attained 

Age]>'70' And [Attained Age]<='75','71-75',[Attained Age]>'75' And [Attained Age]<='80','76-80',[Attained Age]>'80' And [Attained Age]

<='85','81-85',[Attained Age]>'85' And [Attained Age]<='100','85-100',[Attained Age]<'0' Or [Attained Age]>'100','>100')

For ages 0-85 it is fine, but for an age of 93 for example it goes into > 100 group. (I want it to go into 85-100).

btw I put below 0 in the >100 group and that is fine. Why are ages between 85-100 going to 100+?

 

[bentley1]

btw this code works

IIf([Attained Age] >= 100, ">100",IIf([Attained Age] >= 85, "86-100", 
IIf([Attained Age] >= 80, "81-85", 
IIf([Attained Age] >= 75, "76-80", 
IIf([Attained Age] >= 70, "71-75",
IIf([Attained Age] >= 65, "66-70",
IIf([Attained Age] >= 60, "61-65",
IIf([Attained Age] >= 55, "56-60",
IIf([Attained Age] >= 50, "51-55",
IIf([Attained Age] >= 0, "<50", "below 0 - error"))))))))))

but it switch faster?

 

[Special-K99]

Try changing this

[Attained Age]<='50'

to this

[Attained Age]<=50

ie no single quotes.

You'll need to do the same with all the other values

 

[Joe4]

Personally, I would probably create a UDF for such a function. Much easier to read, maintain, and re-use.

 

[bentley1]

Try changing this

[Attained Age]<='50'

to this

[Attained Age]<=50

ie no single quotes.

You'll need to do the same with all the other values

thanks this worked! not sure what the quotes are doing...

 

[bentley1]

Personally, I would probably create a UDF for such a function. Much easier to read, maintain, and re-use.

like in VBA? This part of the query is a small part of a group of queries that get ran and I'm not sure if I would be able to do that haha

 

[Joe4]

like in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>? This part of the query is a small part of a group of queries that get ran and I'm not sure if I would be able to do that haha

Yes, you can create your own functions in VBA that can be used like any other function in queries, forms, reports, etc.

Some of the advantages include:
- much easier to read and maintain (can structure it in a more readable form)
- easy to re-use

For example, let's say that you are currently using it as a Calculated field in three different queries. Now, let's say that you need to change it. You would need to change it in all three queries. Whereas, if it was UDF, you probably only need to change it in one place (the UDF).

Here is an article on it: VBA Tips: Build Custom Functions for Your Access Applications