I need something like an IIF function

P

Pete Rothery

I need something like an IIF function to return one of six options. I need to
take a persons age and return one of six age dependant codes (A thu F). is
there such a thing?

Any help much appreciated

Pete
 
R

Rick Brandt

Pete said:
I need something like an IIF function to return one of six options. I
need to take a persons age and return one of six age dependant codes
(A thu F). is there such a thing?

Any help much appreciated

Pete
Click to expand...

Check help file for the Switch() function.
 
P

Pete Rothery

I don't seem to have a Switch() function - using Office 2003.
Search gave nothing an it not on full list of functions - so I missing
something?
 
K

Ken Sheridan

The codes are data, so are far better stored in a table than hard coded. Not
only does this conform with the fundamental 'information principle' of the
relational model (that all data are stored as values at column positions in
rows in tables and in no other way), but also at a practical level it makes
for easy maintenance. Create a table AgeCodes with columns LowerAge,
UpperAge and AgeCode. You can then join your 'people' table to the AgeCodes
table to return the code for each person, e.g.

SELECT FirstName, LastName, Age, AgeCode
FROM People INNER JOIN AgeCodes
ON People.Age >= AgeCodes.LowerAge
AND People.Age <= AgeCodes.UpperAge;

Don't be tempted to use a BETWEEN….AND operation in the JOIN clause; it
won't work in Access (though it does in standard SQL products). It can be
done as a join criterion in the WHERE clause, however:

SELECT FirstName, LastName, Age, AgeCode
FROM People, AgeCodes
WHERE People.Age BETWEEN AgeCodes.LowerAge
AND AgeCodes.UpperAge;

but this query would not be updatable.

You could also, albeit less efficiently, write a function which accepts the
age as an argument:

Function GetAgeCode (intAge as Integer) As String

Dim strCriteria As String

strCriteria = intAge & " >= LowerAge " & _
"And " & intAge & " <= UpperAge"

GetAgeCode = DLookup("AgeCode", "AgeCodes", strCriteria)

End Function

Ken Sheridan
Stafford, England
 
G

Guest

Don't be tempted to use a BETWEEN..AND operation in the JOIN clause;
it won't work in Access (though it does in standard SQL products). It can
Click to expand...
be

That is to say, it won't work in the Access Query By Example grid,
and it requires brackets ( ), which are not required in other less
common SQL products.

The requirement for (brackets) in Jet SQL is pretty general: most SQL
ported from other products to Access won't work unless brackets are
added around each SQL clause.

SELECT FirstName, LastName, Age, AgeCode
FROM People INNER JOIN AgeCodes
ON (People.Age BETWEEN AgeCodes.LowerAge
AND AgeCodes.UpperAge);

In Jet SQL, queries like this are not updatable unless there is
a one-to many or one-to-one index on the matching fields. Since
this is a many-to-many join it creates a not-updateable recordset.

(david)
 
Top