how to create a field based on many different conditions

[Ami]

Hi, I'm trying to create a field based on many different conditions in Make
Table Query in Access 2003, e.g., if VAR1 and VAR2 meet certain condition,
assign a value "A" to the new field "Category"; if VAR1 and VAR2 meet other
condition, assign a value "B" to "Category" so on and so forth. I keep
getting separate field for each condition, something like "Expr1023". Please
help! Thank you!

 

[Duane Hookom]

Since you haven't provided the expression or SQL statement, I will assume it
is quite complex. I would remove the complexity from the query and create a
user-defined function that accepts some fields as arguments and returns the
appropriate value.

 

[John W. Vinson]

Hi, I'm trying to create a field based on many different conditions in Make
Table Query in Access 2003, e.g., if VAR1 and VAR2 meet certain condition,
assign a value "A" to the new field "Category"; if VAR1 and VAR2 meet other
condition, assign a value "B" to "Category" so on and so forth. I keep
getting separate field for each condition, something like "Expr1023". Please
help! Thank you!

Take a look at the Switch() function. It takes arguments in pairs, and
evaluates the pairs left to right. When it first encounters a pair in which
the first member is TRUE it returns the second member. So you could have an
expression like

Category: Switch([VAR1] = 1 AND [VAR2] = 1, "A", <some other expression
involving the two VARs>, "B", <another expression>, "C", <and so on>)


John W. Vinson [MVP]

 

[Ami]

Thank you so much John! It works!
Ami

Hi, I'm trying to create a field based on many different conditions in Make
Table Query in Access 2003, e.g., if VAR1 and VAR2 meet certain condition,
assign a value "A" to the new field "Category"; if VAR1 and VAR2 meet other
condition, assign a value "B" to "Category" so on and so forth. I keep
getting separate field for each condition, something like "Expr1023". Please
help! Thank you!

Take a look at the Switch() function. It takes arguments in pairs, and
evaluates the pairs left to right. When it first encounters a pair in which
the first member is TRUE it returns the second member. So you could have an
expression like

Category: Switch([VAR1] = 1 AND [VAR2] = 1, "A", <some other expression
involving the two VARs>, "B", <another expression>, "C", <and so on>)


John W. Vinson [MVP]