If/Then - Syntax Question

[MTFlyer]

I'm trying to do a Query that returns either child, adult or senior, based on
teh calculated age in the same query, column titled Age - I have the below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")

 

[Sylvain Lafontaine]

The IIF() function must always have three parameters; so try something like
this (untested):

IIf([Age]<=21,"Child", IIf([Age]<60,"Adult", "Senior"))


Also, when you want to test for two or more conditions with an AND, you
cannot use the following syntax:

IIf([Age]>21 And <60,"Adult", "Senior")

but you must write instead:

IIf([Age]>21 And [Age]<60, "Adult", "Senior")

 

[Douglas J. Steele]

Nest the Iif statements:

IIf([Age]<=21,"Child",IIf([Age]>21 And [Age] <60,"Adult","Senior"))

Another alternative is to use the Switch statement:

Switch([Age]<=21, "Child", [Age]>21 And [Age]<60, "Adult", [Age] >=60,
"Senior")

 

[fredg]

I'm trying to do a Query that returns either child, adult or senior, based on
teh calculated age in the same query, column titled Age - I have the below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")

You need to include the criteria field in each part of the "And"
clause.
As there are only 3 possible choices, if the [Age] is not under 21 nor
between 21 and 60 it must be "Senior". No need to test for it.
Also your parenthesis placements were not correct.

NewColumn:IIf([Age]<=21,"Child",IIf([Age]>21 and [Age] <
60,"Adult","Senior"))

If [Age] is the name of a field in your table storing a person's age,
you are making a mistake It's bound to be incorrect at least once per
year.
It would be better to have a "Date of Birth" [DOB] field, then
calculate the age in the query. That way, the age will always be
correct.

To correctly calculate a person's age using a [DOB] field:

In a query:
Age: DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Directly as the control source of an unbound control in a report or on
a form:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Where [DOB] is the birthdate field.

 

[MTFlyer]

Thank you so much ! This worked perfectly. I was so close and yet so far.

The IIF() function must always have three parameters; so try something like
this (untested):

IIf([Age]<=21,"Child", IIf([Age]<60,"Adult", "Senior"))


Also, when you want to test for two or more conditions with an AND, you
cannot use the following syntax:

IIf([Age]>21 And <60,"Adult", "Senior")

but you must write instead:

IIf([Age]>21 And [Age]<60, "Adult", "Senior")

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I'm trying to do a Query that returns either child, adult or senior, based
on
teh calculated age in the same query, column titled Age - I have the
below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")

 

[MTFlyer]

I have the DOB field in my table. I then have a query that uses DateDiff to
calculate the age, I've now added a second column in my query to group the
resultant ages by child/adult/senior. A little fine tuning on the report
and I got the results that I believe is what I was looking for. Thanks for
your help as well - some great tips to help me with my next query.

I'm trying to do a Query that returns either child, adult or senior, based on
teh calculated age in the same query, column titled Age - I have the below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")

You need to include the criteria field in each part of the "And"
clause.
As there are only 3 possible choices, if the [Age] is not under 21 nor
between 21 and 60 it must be "Senior". No need to test for it.
Also your parenthesis placements were not correct.

NewColumn:IIf([Age]<=21,"Child",IIf([Age]>21 and [Age] <
60,"Adult","Senior"))

If [Age] is the name of a field in your table storing a person's age,
you are making a mistake It's bound to be incorrect at least once per
year.
It would be better to have a "Date of Birth" [DOB] field, then
calculate the age in the query. That way, the age will always be
correct.

To correctly calculate a person's age using a [DOB] field:

In a query:
Age: DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Directly as the control source of an unbound control in a report or on
a form:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

Where [DOB] is the birthdate field.

 

[John... Visio MVP]

Just to be picky, the "[Age]>21 And" in the IIF is redundant. The test has
already been done and this condition is already in the false part of
"[Age]<=2"

Sylvain had it right.

John... Visio MVP

Nest the Iif statements:

IIf([Age]<=21,"Child",IIf([Age]>21 And [Age] <60,"Adult","Senior"))

Another alternative is to use the Switch statement:

Switch([Age]<=21, "Child", [Age]>21 And [Age]<60, "Adult", [Age] >=60,
"Senior")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm trying to do a Query that returns either child, adult or senior,
based on
teh calculated age in the same query, column titled Age - I have the
below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")

 

[Douglas J. Steele]

Yup, you're right, and I should have caught that, since I did drop the >= 60
check.

IIf([Age]<=21,"Child",IIf([Age] <60,"Adult","Senior"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Just to be picky, the "[Age]>21 And" in the IIF is redundant. The test has
already been done and this condition is already in the false part of
"[Age]<=2"

Sylvain had it right.

John... Visio MVP

Nest the Iif statements:

IIf([Age]<=21,"Child",IIf([Age]>21 And [Age] <60,"Adult","Senior"))

Another alternative is to use the Switch statement:

Switch([Age]<=21, "Child", [Age]>21 And [Age]<60, "Adult", [Age] >=60,
"Senior")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm trying to do a Query that returns either child, adult or senior,
based on
teh calculated age in the same query, column titled Age - I have the
below
but I'm getting syntax errors - any suggestions ?

IIf([Age]<=21,"Child") Or IIf([Age]>21 And <60,"Adult") Or
IIf([Age]>=60,"Senior")