decode

[Guest]

hi, can any one tell me how to decode data. i have a field in a query that
contains ethnicity. the data is entered in number form and i want to decode
this so that when my query is run it displays the text (ie, white english
instead of 1). is there a way to do this using the query design builder and
not sql??

 

[Brian Bastl]

rhys,

Basically, you'll need to add your Ethnicity look-up table to the query
grid, and join it to the table already in your query. Then you can replace
the numeric value with its corresponding text value.

HTH,
Brian

 

[Guest]

the tables i am using are linked (odbc) and there isn't a table that contains
ethnicity. or one that i can find anyway. is there another way of doing the
decode. maybe using expression??

rhys

 

[Wayne Morgan]

Yes, you need to create a "calculated field" in the query. In the design
grid you would enter something like this for the field.

EthnicityText:Function([Ethnicity])

The function could be a user defined function or a built-in function. If the
numbers you mention are sequential, I would recommend the Choose() function.

Example:
EthnicityText:Choose([Ethnicity], "White English", "choice 2", "choice 3",
"choice n")

If they aren't sequential, try the Switch() function.

Example:
EthnicityText:Switch([Ethnicity]=1, "White English", [Ethnicity]=4, "choice
2")

Either of these will result in a field in your query's output called
"EthnicityText". There is a colon in the syntax after "EthnicityText".

A "user defined function" would be one that you create in a VBA module. You
would call it just as the function above are called. You would need to pass
the value of the [Ethnicity] field as its argument.

 

[Guest]

thanks for that. i entered the switch version and i got a msgbox saying the
expression was too complex. i have 17 ethnicity choices

Yes, you need to create a "calculated field" in the query. In the design
grid you would enter something like this for the field.

EthnicityText:Function([Ethnicity])

The function could be a user defined function or a built-in function. If the
numbers you mention are sequential, I would recommend the Choose() function.

Example:
EthnicityText:Choose([Ethnicity], "White English", "choice 2", "choice 3",
"choice n")

If they aren't sequential, try the Switch() function.

Example:
EthnicityText:Switch([Ethnicity]=1, "White English", [Ethnicity]=4, "choice
2")

Either of these will result in a field in your query's output called
"EthnicityText". There is a colon in the syntax after "EthnicityText".

A "user defined function" would be one that you create in a VBA module. You
would call it just as the function above are called. You would need to pass
the value of the [Ethnicity] field as its argument.

--
Wayne Morgan
MS Access MVP

hi, can any one tell me how to decode data. i have a field in a query
that
contains ethnicity. the data is entered in number form and i want to
decode
this so that when my query is run it displays the text (ie, white english
instead of 1). is there a way to do this using the query design builder
and
not sql??

 

[Wayne Morgan]

The Switch() function evaluates all expressions in the arguments each time,
so if any of them return an illegal value (such as divide by zero) when
being evaluated you will get an error. Other than that, I would suspect a
typing error (check for extra or missing commas); especially if they're all
just the "[FieldName]=Value" type.

The help file doesn't mention a limit on the number of arguments, but if
there is I would expect it to be 255 or higher.

--
Wayne Morgan
MS Access MVP

thanks for that. i entered the switch version and i got a msgbox saying
the
expression was too complex. i have 17 ethnicity choices

Yes, you need to create a "calculated field" in the query. In the design
grid you would enter something like this for the field.

EthnicityText:Function([Ethnicity])

The function could be a user defined function or a built-in function. If
the
numbers you mention are sequential, I would recommend the Choose()
function.

Example:
EthnicityText:Choose([Ethnicity], "White English", "choice 2", "choice
3",
"choice n")

If they aren't sequential, try the Switch() function.

Example:
EthnicityText:Switch([Ethnicity]=1, "White English", [Ethnicity]=4,
"choice
2")

Either of these will result in a field in your query's output called
"EthnicityText". There is a colon in the syntax after "EthnicityText".

A "user defined function" would be one that you create in a VBA module.
You
would call it just as the function above are called. You would need to
pass
the value of the [Ethnicity] field as its argument.

--
Wayne Morgan
MS Access MVP

hi, can any one tell me how to decode data. i have a field in a query
that
contains ethnicity. the data is entered in number form and i want to
decode
this so that when my query is run it displays the text (ie, white
english
instead of 1). is there a way to do this using the query design builder
and
not sql??