display / return string data instead of code from lookup table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
You could use:
IIf([fldCode]=1,"Yes",IIf([fldCode]=2,"No",Null)) in the field of the query
design grid.

Though generally, 0 should represent No/Off/False and -1 should represent
Yes/On/True
 
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
..
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
Thanks for such a prompt reply, this is really useful. sorry about the
standard coding anomaly - i didn't design this database but just have the
task of getting data out of it! all the best, emma

Darren Bartrup said:
You could use:
IIf([fldCode]=1,"Yes",IIf([fldCode]=2,"No",Null)) in the field of the query
design grid.

Though generally, 0 should represent No/Off/False and -1 should represent
Yes/On/True


Emelina Bumsquash said:
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!
Click to expand...
Click to expand...
 
To have the field from the conversion table show in the query, you should
only need to add fldConvert to the list of fields that are displayed.


I do not know what you mean by all the values in the field seem to be
'resetting' to zero. Which field - the conversion table or the "main"
table.
If you are not seeing the correct values:
-- Check the linking fields to make sure they are both of the same type.
Both number fields
-- Check to make sure that you have matching values in both tables

Post the SQL of the query that is not working (VIEW: SQL from the menu) and
perhaps someone can suggest a change to your query to make it work for you.

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

Emelina Bumsquash said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Hi John,
I figured out what the problem was with the 'resetting' - I was linking more
than one field to the FldValue in the conversion table so I'm guessing that
was the problem.

Thank you, I've used the 'FldConvert' thing and it works great now. Thanks
so much for your help! All the best, Emma

John Spencer said:
To have the field from the conversion table show in the query, you should
only need to add fldConvert to the list of fields that are displayed.


I do not know what you mean by all the values in the field seem to be
'resetting' to zero. Which field - the conversion table or the "main"
table.
If you are not seeing the correct values:
-- Check the linking fields to make sure they are both of the same type.
Both number fields
-- Check to make sure that you have matching values in both tables

Post the SQL of the query that is not working (VIEW: SQL from the menu) and
perhaps someone can suggest a change to your query to make it work for you.

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

Emelina Bumsquash said:
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 said:
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
..

message 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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top