Re: display / return string data instead of code from lookup table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi John, thanks for your answer. I've used the calculated field option for
most of them as it worked easier and i'm not too good at access!

however, i'd like to use the conversion table option on one of the fields to
display longer string data to avoid having to write cumbersome calculations.

i've done a conversion table as you suggested and joined the relevant field
to the FldValue. However, I now have 2 problems:

1. all the values in the field now seem to be 'resetting' to 0.
2. I don't know how to display the FldConvert in the query as you advise in
your reply.

can you help? much appreciated!

"John Spencer" wrote:

You can use a calculated field or you can use a table with with the values
that are stored and the equivalent value to display. The related table is
the most flexible and is generally the easiest to implement.

Calculated field:
Field: ShowYesNo: IIF([FieldName]=1,"No", IIF([FieldName]=2,"Yes",Null))

With the conversion table:
FldValue: (1,2,3, etc)
fldConvert: ("No","Yes","Maybe")

Add the table to your query, join the field in the main table to fldValue in
the conversion table and display FldConvert in the query. If you don't
always have a value in the main table then make the join an outer join by
double clicking on the join line and selecting the option for ALL records in
the main table and only matches in the conversion table.

One additional option is to write a VBA function that you can call and pass
the value to and then have it return the equivalent value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Emelina Bumsquash" <EmelinaBumsquash@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:77EA4B9D-AD1E-46C3-B9B1-4ABA3BF127E5@xxxxxxxxxxxxxxxx
Is there any way to get a query to return string data rather than code
even
when the code data is stored in the table? i.e For a given field if "no" =
1
and "yes" = 2, the query currently returns the '1' or '2' (given that this
is
the value stored in the table). However, I'd like it to return "yes" or
"no"
instead if possible? can anyone help? I know this sort of thing can be
accomplished if I make a report based on the query but I'd really like to
do
it directly in the query if possible?
Thanks in advance for any help!



.

Relevant Pages

  • Re: User defined function in SQL statement
    ... I'm taking your advice and trying to incorporate the second table in the SQL ... CONVERSION TABLE] AS B ... calculated field for both Existing and Proposed so I had to move the Nz and ... Query works. ...
    (microsoft.public.access.queries)
  • Re: User defined function in SQL statement
    ... FROM TEMPTABLEA INNER JOIN [UNITS CONVERSION TABLE] ... I'm getting 3 records in the query results instead ... > calculated field for both Existing and Proposed so I had to move the Nz ...
    (microsoft.public.access.queries)
  • Re: updatable form with nou-updatable sub or main forms
    ... > query as a datasource and the query have a number of calculated fields. ... Since the conversion, I have added another calculated field on to ... > know that error message in not true because I have triple checked this and ...
    (microsoft.public.access.forms)
  • Re: Totaling dates in a subform
    ... "John W. Vinson" wrote: ... not be a query you wrote, but if not, it's one that Access created for you. ... I thought a calculated field had to be an unbound ...
    (microsoft.public.access.modulesdaovba)
  • Re: Still Stuck on Alpha Numerics
    ... Thanks John, ... only the last calculated field which shows the error. ... This is the SQL text ... >>I have added ascending to each query sort field (except for the calculated ...
    (microsoft.public.access.tablesdbdesign)