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??

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


"rhys" <(E-Mail Removed)> wrote in message
news:72BCEB27-9970-4793-A4EA-(E-Mail Removed)...
> 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??

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

"Brian Bastl" wrote:

> 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
>
>
> "rhys" <(E-Mail Removed)> wrote in message
> news:72BCEB27-9970-4793-A4EA-(E-Mail Removed)...
> > 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??
>
>
>

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


"rhys" <(E-Mail Removed)> wrote in message
news:72BCEB27-9970-4793-A4EA-(E-Mail Removed)...
> 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??

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

"Wayne Morgan" wrote:

> 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
>
>
> "rhys" <(E-Mail Removed)> wrote in message
> news:72BCEB27-9970-4793-A4EA-(E-Mail Removed)...
> > 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??
>
>
>

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


"rhys" <(E-Mail Removed)> wrote in message
news:C46F5EBE-AD5E-4B95-BD2D-(E-Mail Removed)...
> thanks for that. i entered the switch version and i got a msgbox saying
> the
> expression was too complex. i have 17 ethnicity choices
>
> "Wayne Morgan" wrote:
>
>> 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
>>
>>
>> "rhys" <(E-Mail Removed)> wrote in message
>> news:72BCEB27-9970-4793-A4EA-(E-Mail Removed)...
>> > 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??
>>
>>
>>