SQL for changing number into text without UPDATE

  • Thread starter Thread starter Student Databaser
  • Start date Start date
S

Student Databaser

I have a field [PRACTICE AREA] which is formated as field type - number , but
it is a combo box where the value list is like this
1;"PHY";2;"CHW";3;"PHL";4;"DENTIST";5;"SW";6;"EBP";7;"VET";8;"EH";9;"HIS";10;"EPID";11;"LS";12;"HA";13;"MHSA";14;"HEP";15;"NUT";16;"PHN";17;"OTHER";18;"PHP"

So when i run queries it returns the results in numbers. I want to run a
crosstab query in effort to create a report - therefore the numbers are not
very useful i need the text instead.

How do i do this without updating the table or changing the format of this
field?

I thought i could do a SWITCH query but i don't really understand how the
SQL should look for that. This is a work in progress but here is what i am
playing around with. I know i am missing a lot of things.

SELECT DEMOGRAPHIC.[PRACTICE AREA]
FROM DEMOGRAPHIC
SWITCH (1,"PH EDUC",[2,PH ADMIN]);
 
Actually, the best way to do this is get rid of your ValueList data source
and create a table for these values. Then refer to the table as the
rowsource for your combo box, and set its Control Source to the [Practice
Area] field.

HTH
Dale

Don't forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
The easiest way would be to build a table PracticeAreas with two or three fields
NumberField (AreaID) - number id
TextField (AreaCode) - Short abbreviation for area
TextField (AreaName) - Full name of area

Now you can use that table to populate the combobox and use that table in your
query Joining Demographic.[Practice Area] to PracticeAreas.AreaID

If you cannot add the additional table, then life will be more difficult. You
can probably build a query using the choose function
SELECT DEMOGRAPHIC.[PRACTICE AREA],
Choose([Practice Area],"PHY","CHW","PHL",...,"PHP") as AreaCode
FROM DEMOGRAPHIC


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Dale Fye said:
Actually, the best way to do this is get rid of your ValueList data source
and create a table for these values. Then refer to the table as the
rowsource for your combo box, and set its Control Source to the [Practice
Area] field.

HTH
Dale

Don't forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Student Databaser said:
I have a field [PRACTICE AREA] which is formated as field type - number , but
it is a combo box where the value list is like this
1;"PHY";2;"CHW";3;"PHL";4;"DENTIST";5;"SW";6;"EBP";7;"VET";8;"EH";9;"HIS";10;"EPID";11;"LS";12;"HA";13;"MHSA";14;"HEP";15;"NUT";16;"PHN";17;"OTHER";18;"PHP"

So when i run queries it returns the results in numbers. I want to run a
crosstab query in effort to create a report - therefore the numbers are not
very useful i need the text instead.

How do i do this without updating the table or changing the format of this
field?

I thought i could do a SWITCH query but i don't really understand how the
SQL should look for that. This is a work in progress but here is what i am
playing around with. I know i am missing a lot of things.

SELECT DEMOGRAPHIC.[PRACTICE AREA]
FROM DEMOGRAPHIC
SWITCH (1,"PH EDUC",[2,PH ADMIN]);
Click to expand...
Click to expand...

When i did this it changed the way you enter data into the table. I want
everything about the table to remain the same, and just to be able to
manipulate the data in queries. Maybe i misunderstood your instructions. Any
further advice or clarification would be appreciated.

Thanks
 
Back
Top