SQL Problem w/ List Box in Form 1

[tyleri]

Hi - I have 2 values in a field in my table: 100 and 200

100 = External User
200 = Internal User

I am calling up "active users" in a list box with this code:

SELECT [REAL_TIME] &" - " & [First_Name] & " " & [Last_Name]& " - " & [Login_ID] & " - " & [User_Access_Value] AS entryname FROM [dbo_v_ActiveUsers];

When it puts either 100 or 200 (in the User_Access_Value field) I want it to show the word Internal or External instead.

So the final product would be:
12:30pm - Tyler Ingersoll - tinger - Internal

instead of:
12:30pm - Tyler Ingersoll - tinger - 200

So, what code would I insert for it to convert 100 to EXTERNAL and 200 to INTERNAL ?

Thanks for your help in advance!

 

[kathryn]

IMHO, the best way is to create another table with just two fields, User_access_value and TextValue. The table would have just two records

User_Access_Value TextValue
100 Internal
200 External

Then add this to the query linking the two User_Access_Value fields. Remove the User_Access_Value from the query results and add TextValue.

Kathryn

 

[camel66]

I want to do a similar thing but will be doing it many times so do not want to create a ton of mini tables with the text values. My fields are combo boxes with two columns, i store a number code in my table but set the first column width to 0 to hide the code and display the text. when I try and attach this field to another it displays the number instead of the text. I am trying to use the column property so the text in the second column will be displayed but i keep getting a error message that lutravdest.strinter.column(1) is and invalid function.

Help

here is the SQL statement

select lutravdest.alpha3, lutravdest.dest & " " lutravdest.strinter.column(1)
order by lutravdest.dest

 

[camel66]

select lutravdest.alpha3, lutravdest.dest & " " lutravdest.strinter.column(1)as destination
order by lutravdest.dest

I left some of the previous SQL out, here is the full statement