Convert Numeric value to text

[terbs]

Im trying to construct a query which returns a field that has 21 different types of values.

Is there a way to assign a text value to each numeric value when the results are displayed?

Eg: 21 = "Local Sales", 22 = "National Sales", 23 = "International Sales".. and so on.

Ive looked at the Choose and IIF functions but I cant seem to work it out

..............

Anouther little problem, which should be basic. I want to exclude certain records with values such as 31 and 41 and 42.

When i type <> 31 or <> 41 <> 42 in the criteria they are all still displayed. Although when I type one of those criteria by themselves they ARE EXCLUDED. Is there something dumb I am missing here??

thanks in advance.

 

[pbaldy]

With that many values, I'd put them in a table. Then you can simply join that table to your other table and return the text value.

Second issue, you want AND instead of OR. In SQL view it would look like:

WHERE FieldName <> 31 AND FieldName <> 41 AND ...

 

[MCastillo]

Paul,

I'm having a similiar problem with a query and wondered if you might be able to help. I created a query that includes fields that are linked to other tables of items. The query runs correctly, but one of the fields is not displaying the text. It displays numbers, which it looks like is the order of the items in the table.

Any suggestions on how I can get the field to display the text instead of the number? One of the other tables in the query is properly displaying the text so can't seem to figure out why this one doesn't.

Appreciate any guidance you can provide.

 

[boblarson]

Paul,

I'm having a similiar problem with a query and wondered if you might be able to help. I created a query that includes fields that are linked to other tables of items. The query runs correctly, but one of the fields is not displaying the text. It displays numbers, which it looks like is the order of the items in the table.

Any suggestions on how I can get the field to display the text instead of the number? One of the other tables in the query is properly displaying the text so can't seem to figure out why this one doesn't.

Appreciate any guidance you can provide.

Sounds like you have some lookups at table level. See here for more on that:

http://www.mvps.org/access/lookupfields.htm

Remove the lookups at table level and use them only on forms.

 

[JohnLee]

Hi Folks,

I have a table that is linked to 3 other tables via an append query that uses a lookup feature as follows

There are 3 fields in the main table that are of the exact data type as one field in each of the 3 lookup tables.

The relationship I have created between these tables is "only include rows where the join fields from tables are equal"

In the fields where I want the required data to appear I have created the following expression in the field criteria of the table appending from:

Field: Company: IIf([strLetterCode2Pt2]=[strCompanyType],[strCompany])
Table:
Sort:
Append To: strCompany
Criteria:
Or:

This checks to see if the "strLetterCode2Pt2" matches the any of the contents of the field "strCompanyType" field in the tblCompanyTypes table and if it does it assigns the value of "strCompany" to the "strCompany" field of the table being appended to.

The data in the "strLetterCode2Pt2" must match exactly the data in the lookup table.

hope this is of assistance

John

 

[apr pillai]

IIF problem

Field: Company: IIf([strLetterCode2Pt2]=[strCompanyType],[strCompany])

You didn't say what should do if the comparison doesn't match.

Field: Company: IIf([strLetterCode2Pt2]=[strCompanyType],[strCompany], ????)

Qualify the Field Names with its Table Name if the same field name appearing in more than one table i.e.

Company: iif([table1name].[field1name] = [table2name].[fieldname2],[tablename3].[strCompany], [tablename4].[strcompany])

 

[JohnLee]

Hi,

In my case it will always match, because I have done all the necessary investigations to take account of possible no matching and set it up accordingly, in your case, just put in a further criteria if it doesn't match, you will need to create a process that enables you to outsort/identify those that do not match.

You need to make sure that you identify all possibilities of mis or no matching and build that into your process.

I have set up my lookup tables so that the same name never appears in more than one table, that way there is no possibility of an incorrect lookup.

If you have the same name in more than one table, then perhaps you need revise your tables with a view to improving your normalisation.


John