IIF function

[junkforhr]

Hi guys,

I am new to Acces ( one week in fact). I've been searching to web about Access and have found the IIF function.

I have a column of data with customers ages. What I am trying to do in query is group these customers by age groupings.

I have tried this:

Aged 0 to 17: IIf([Customer age]>=0 And <=17,"0 to 17," ")

But it comes up with an error: "The expression you entered contains invalid syntax".

Being very new to Access, I am not sure what I am doing is possible.

Can some please provide some assistance

 

[james_lankford]

try

Aged 0 to 17: IIf([your table name].[Customer age]>=0 And [your table name].[Customer age]<=17,"0 to 17," ")

 

[junkforhr]

Thanks James worked a treat

Cheer

 

[Norie]

You could use Between.

AgeGroup:Iif([AgeField] Between 0 And 17, "0-17")

There is another option - create a table with all possible ages and corresponding age groups.

Something like this perhaps.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>AgeGroup</TH><TH bgColor=#c0c0c0 borderColor=#000000>Age</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>6</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>7</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>8</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>9</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>12</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>13</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>14</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>15</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>16</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>0-17</TD><TD borderColor=#c0c0c0 align=right>17</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>18-35</TD><TD borderColor=#c0c0c0 align=right>18</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>18-35</TD><TD borderColor=#c0c0c0 align=right>19</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>18-35</TD><TD borderColor=#c0c0c0 align=right>20</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>18-35</TD><TD borderColor=#c0c0c0 align=right>21</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>18-35</TD><TD borderColor=#c0c0c0 align=right>22</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

This can then be used in 2 ways.

1 Add the table in a query and link the age fields.

2 Use DLookup, eg AgeGroup: DLookUp("[AgeGroup]","AgeGroups","[Age]=" & [CustAge])

SELECT Customers.CustName, Customers.CustAge, DLookUp("[AgeGroup]","AgeGroups","[Age]=" & [CustAge]) AS AgeGroup
FROM Customers;

 

[SydneyGeek]

Another option is to use the Partition function, if the intervals will be evenly spaced (eg 0-25, 26-51, ...)

You define the field, the upper and lower limits, and the interval size.

Denis

 

[Norie]

Denis

The Partition function?

Access 2007?

Still like the table idea mind you and wish Access had functions like the LOOKUP worksheet function.

 

[SydneyGeek]

Denis

The Partition function?

Access 2007?

Still like the table idea mind you and wish Access had functions like the LOOKUP worksheet function.

Yes, Partition, absolutely, in Access 2007. Just tried it to make sure, in case it was something that got chucked out. Syntax is Partition([Field],LowValue,HighValue,Interval)

I also like the table idea -- tend to use that for dates, especially when looking at using every date in a range.

Denis

 

[Norie]

Denis

I'll need to look into that function, but I still prefer the table approach.

One advantage is that you can change the age limits or the text for the groups and you don't have to go and change the expression(s).

One disadvantage might be having to set up the table in the first place, but that should only be a one-off and it only took me a couple of minutes in Excel.

In fact I used the LOOKUP function there for the age groups, sort of cheating I suppose.

 

[SydneyGeek]

I use defined tables like this all the time, and yes they are easy to use. In many cases they are my preference, because editing the table is a simple way to change the groupings.

The reason for showing the Partition function was to show an alternative, because I always like to know if there's another approach. One thing I like about Partition is that values outside the upper and lower limits will still display; you get < and > ranges as part of the result set.

Denis

 

[Norie]

Denis

Good point.

I think that's where a function like Nz could come in useful.