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

 

[junkforhr]

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 bgcolor="#ffffff" border="1" cellspacing="0"><caption>
</caption><thead><tr><th bordercolor="#000000" bgcolor="#c0c0c0">AgeGroup</th><th bordercolor="#000000" bgcolor="#c0c0c0">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;

Thanks as well Norrie. This idea is best suited to my needs.

Cheers