convert number field to a text equivalent

Do you mean a Make Table or Update query?

For an UPDATE:

UPDATE YourTable SET product_levelText = "Capital Directions"  WHERE Product_Level =69

Select allOpen in new window

UPDATE YourTable SET product_levelText = "Portfolio Solutions"  WHERE Product_Level =67

Select allOpen in new window

For a Make table query:

SELECT  Field1, Field2, ProductLevel "Portfolio Solutions" AS product_levelText  WHERE ProductLevel  = 67 INTO YourTable

Select allOpen in new window

SELECT  Field1, Field2, ProductLevel "Capital Directions" AS product_levelText  WHERE ProductLevel  = 69 INTO YourTable

Select allOpen in new window

You would need to run as many queries as number to text operations you have...

If there is some logic to how the numbers correlate to the text, you may be able to code a generic solution, otherwise you would need distinct queries for each number.

Add another table to your database with the following:

tblProductLevels
Level - Number - Long
Description - Text

 now in your make table query, add this table to the query, join Level to your main record, then pull down the description field into what ever you need it for.

If this is a one time deal, you could also use the Replace() function as a definition for a column, but if you have a lot of them (more then a half dozen or so), you'd be better to use the table.

Your other option would be to call a function from the query, pass the level and return a description.  Then you could have as many If's/Case statements as you needed.

Jim.

Or even leave the existing table as is if this is the way the data comes in and link it to JDettmans descriptor table for reporting. That way you retain flexibility if there are changes or additions in the future.

Chris B

Why am I selecting Field1, Field2??

>> Why am I selecting Field1, Field2??

Those are hypothetical field names.  Without knowing your table structure, I guessed that you may have other fields that need to be selected and added to your new table.

This is a one time thing, and the possible values are only 5..

69  = Capital Directions
24  = Portfolio
25  = integrated
26 = Strategic
59 = Alternative