Convert number to text

[Bertrand82]

I have a query that contants a field with numbers.

I would like the query to replace the numbers with text. eg.

25 = North America
60 = Europe
90 = Asia



Should i use the expression builder if yes, whats the code?

Br Bertrand

[June7]

Only 3 items?

Try:

Switch(fieldname=25,"North America", fieldname=60,"Europe", fieldname=90, "Asia")

Alternatively, have a table of this data and include that table in query by joining on the number fields.

[Bertrand82]

Switch("fieldname"=25;USA)

gives me data type mismatch in criteria expression

[June7]

Use your actual field name instead of fieldname. Is it a number or text type? Literal strings must be in quote marks per my example: "USA"

[Bertrand82]

I have realized that this is a little bit more complicated than first thougt and will therefor mark this thread as solved. thanks anyway,

Br

[Bertrand82]

Im back


Below you find the code from my union Query. The field "Company" within table GI brazil/GI USA/GI Korea contains numbers 25, 60, and 90.

I would like the query to replace the numbers with 25 = USA, 60 = Brazil, 90 = Korea.

Can someone please insert the correct code so I can copy and paste in SQL view?

Br Bertrand


SELECT [GI brazil].Company, [GI brazil].ID, [GI brazil].First_name, [GI brazil].Family_name, [GI brazil].[Hire_ date]
FROM [GI brazil];
UNION ALL SELECT [GI USA].Company, [GI USA].ID, [GI USA].First_name, [GI USA].Family_name, [GI USA].[Hire_ date]
FROM [GI USA];
UNION ALL SELECT [GI KOREA].Company, [GI KOREA].ID, [GI KOREA].First_name, [GI KOREA].Family_name, [GI KOREA].[Hire_ date]
FROM [GI KOREA]
ORDER BY Company, Family_name, First_name;

[June7]

Why do you have 3 tables GI brazil/GI USA/GI Korea? I don't understand data structure.

If you want to provide file for analysis, follow instructions at bottom of my post.

[ssanfu]

Your structure is not normalized. What would you do if you had to add another country - say France?? Currently, you would have to add another table, modify your queries, forms and reports.

If you structure was normalized, you would have one table to hold the names, hire date and company, with an additional field for the country.

So lets call the table "tblGI". And the fields would be

ID  -  Autonumber (PK)  
First_name  - Text
Family_name - Text
Company - Text ( or Long - FK to another table)
Hire_date - Date/Time  (no space in the name)
Country - Text ( or Long - FK to another table)

Then your query would look like

SELECT tblGI.ID, tblGI.Company, tblGI.First_name, tblGI.Family_name, tblGI.Hire_date, tblGI.Country
FROM GI
ORDER BY tblGI.Company, tblGI.Family_name, tblGI.First_name;

If you wanted to add someone from another country, it would be a simple process, just add the data