I need something like an IIF function

[Guest]

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

 

[Rick Brandt]

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

Check help file for the Switch() function.

 

[Guest]

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?

 

[Rick Brandt]

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?

It;s in VBA help, not Access help. Do this.

Press Ctl-G to bring up the VBA editor window.
In the immediate pane type Switch()
Put your cursor back in the word "Switch" and press F1.

That is the best way to find the help topic on a VBA function. You should be
taken directly to it. The topic will be similar to...

Syntax

Switch(expr-1, value-1[, expr-2, value-2 . [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding
expression is True.

Remarks

The Switch function argument list consists of pairs of expressions and values.
The expressions are evaluated from left to right, and the value associated with
the first expression to evaluate to True is returned. If the parts aren't
properly paired, a run-time error occurs. For example, if expr-1 is True, Switch
returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2,
and so on.

Switch returns a Null value if:

· None of the expressions is True.
· The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of
them. For this reason, you should watch for undesirable side effects. For
example, if the evaluation of any expression results in a division by zero
error, an error occurs.

 

[missinglinq via AccessMonster.com]

Here's the example from Access Help that maybe makes it a little clearer:

Function MatchUp (City As String)
Matchup = Switch(City = "London", "English", City = "Rome"), "Italian")
End Function

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

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

 

[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

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)