Populate field based on If statement

[Guest]

I am trying to populate a field based on an IIF statement in a query. My
problem is I need to be able to create an expression string.

I need basically for the field to populate itself with different criteria
based on a string of IIF statements.

Basics are that if the Age field is less than or equal to 3, then populate
Grade field as ES and if the Age field is equal to 4 populate Grade field
with PK and if Age field is equal to or greater than 5 popoulate Grade field
with K, etc.

(Age field data is dervied from an update query where the expression is
written to return the age of a person based on today's date and their
birthdate.)

I wrote the following expression in a query:

=IIf([Age]<=3, "ES") and it works just fine, but if I try to add additional
expressions so that I can get one thing if a condition is true, or another if
another condition is true, I am getting a syntax error.

I tried this but this is where I get the syntax error:

=IIf([Age]<=3, "ES"), IIf([Age]=4, "PK"), IIf([Age]>=5, "K")

I know I have written a string of IIf statements before, but i can't
remember how.

I also tried writing separate expressions in the query, which do return the
correct data, but then, of course, it is all in different fields. Is there a
way to maybe do a make table query based on the separate expressions, then
write some sort of query that would put them all in one field?

Thanks so much for any help anyone can give!

 

[Jeff Boyce]

Why are you trying to "populate" a field with a "calculated" value? In most
cases, it is neither necessary nor desirable. Moreover, why do you have an
"Age" field? This is also a calculated value, and, to be completely
up-to-date, would need to be recalculated & refreshed daily. This, too, is
an excellent candidate for calculation in a query.

Instead, you could use a query to calculate the value on the fly. The
expression might look something like:

IIF([YourCalculatedAge] <= 3, "ES",IIF([YourCalculatedAge] = 4,"PK",...)

Or you could consider writing a procedure/function that uses a SELECT CASE
statement to help you determine the "code"... You could then call that
function from within your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You need to provide nested IIF statements, something similar to this:

= IIf(Age<=3,"ES",IIf(Age=4,"PK",IIf(Age>=5,"K")))

Assuming that the Age field can never be Null, of course.

Ray

 

[Guest]

IIf([Age]<=3,"ES",IIf([Age]=4,"PK",IIf([Age]=5,"K","")))

 

[Guest]

Hi Jeff

I have made many calculated fields - so users can see up to date
information. If her (assuming the post was from a "her") age field is a
calculted result is there a problem with using this to show references to a
user. Would appreciate to feedback as I may have been doing it wrong. The
problem is, is that I can always going tthe tables and "have a look" a user
FE can't so I feel you really do need calculated field in the real world.


--
Wayne
Manchester, England.
Enjoy whatever it is you do

Why are you trying to "populate" a field with a "calculated" value? In most
cases, it is neither necessary nor desirable. Moreover, why do you have an
"Age" field? This is also a calculated value, and, to be completely
up-to-date, would need to be recalculated & refreshed daily. This, too, is
an excellent candidate for calculation in a query.

Instead, you could use a query to calculate the value on the fly. The
expression might look something like:

IIF([YourCalculatedAge] <= 3, "ES",IIF([YourCalculatedAge] = 4,"PK",...)

Or you could consider writing a procedure/function that uses a SELECT CASE
statement to help you determine the "code"... You could then call that
function from within your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to populate a field based on an IIF statement in a query. My
problem is I need to be able to create an expression string.

I need basically for the field to populate itself with different criteria
based on a string of IIF statements.

Basics are that if the Age field is less than or equal to 3, then populate
Grade field as ES and if the Age field is equal to 4 populate Grade field
with PK and if Age field is equal to or greater than 5 popoulate Grade
field
with K, etc.

(Age field data is dervied from an update query where the expression is
written to return the age of a person based on today's date and their
birthdate.)

I wrote the following expression in a query:

=IIf([Age]<=3, "ES") and it works just fine, but if I try to add
additional
expressions so that I can get one thing if a condition is true, or another
if
another condition is true, I am getting a syntax error.

I tried this but this is where I get the syntax error:

=IIf([Age]<=3, "ES"), IIf([Age]=4, "PK"), IIf([Age]>=5, "K")

I know I have written a string of IIf statements before, but i can't
remember how.

I also tried writing separate expressions in the query, which do return
the
correct data, but then, of course, it is all in different fields. Is
there a
way to maybe do a make table query based on the separate expressions, then
write some sort of query that would put them all in one field?

Thanks so much for any help anyone can give!

 

[Jeff Boyce]

Wayne

I'm not saying "bad", I'm saying "rarely necessary and causes much
additional work to keep synchronized & up to date."

If I use a form, based on a query, rather than directly on a table, I can do
the "age" calculation in the query and display age on the form. No daily
updates required, no manual updates required, the query just does the work,
and the user sees the age.

In Access, tables store data, but have a very impoverished event model.
Forms in Access display data and have a very rich event model.

If you have your users working directly in your tables, you're likely to run
into the "well-intentioned-but-totally-clueless-user" who thinks that the
table is a spreadsheet and s/he can treat it as s/he would Excel.

If you only present forms (and reports) to your users, you have much more
control over how they interact with the data, and can make their data
entry/edit job much easier.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

I have made many calculated fields - so users can see up to date
information. If her (assuming the post was from a "her") age field is a
calculted result is there a problem with using this to show references to
a
user. Would appreciate to feedback as I may have been doing it wrong.
The
problem is, is that I can always going tthe tables and "have a look" a
user
FE can't so I feel you really do need calculated field in the real world.


--
Wayne
Manchester, England.
Enjoy whatever it is you do

Why are you trying to "populate" a field with a "calculated" value? In
most
cases, it is neither necessary nor desirable. Moreover, why do you have
an
"Age" field? This is also a calculated value, and, to be completely
up-to-date, would need to be recalculated & refreshed daily. This, too,
is
an excellent candidate for calculation in a query.

Instead, you could use a query to calculate the value on the fly. The
expression might look something like:

IIF([YourCalculatedAge] <= 3, "ES",IIF([YourCalculatedAge] =
4,"PK",...)

Or you could consider writing a procedure/function that uses a SELECT
CASE
statement to help you determine the "code"... You could then call that
function from within your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to populate a field based on an IIF statement in a query.
My
problem is I need to be able to create an expression string.

I need basically for the field to populate itself with different
criteria
based on a string of IIF statements.

Basics are that if the Age field is less than or equal to 3, then
populate
Grade field as ES and if the Age field is equal to 4 populate Grade
field
with PK and if Age field is equal to or greater than 5 popoulate Grade
field
with K, etc.

(Age field data is dervied from an update query where the expression is
written to return the age of a person based on today's date and their
birthdate.)

I wrote the following expression in a query:

=IIf([Age]<=3, "ES") and it works just fine, but if I try to add
additional
expressions so that I can get one thing if a condition is true, or
another
if
another condition is true, I am getting a syntax error.

I tried this but this is where I get the syntax error:

=IIf([Age]<=3, "ES"), IIf([Age]=4, "PK"), IIf([Age]>=5, "K")

I know I have written a string of IIf statements before, but i can't
remember how.

I also tried writing separate expressions in the query, which do return
the
correct data, but then, of course, it is all in different fields. Is
there a
way to maybe do a make table query based on the separate expressions,
then
write some sort of query that would put them all in one field?

Thanks so much for any help anyone can give!