By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Please Help, Need Change Text Values For a Field in a Table

P: 26
Hi

In my database, I use data that is imported XML data. One of the feilds in my table has text data defined with only two values -->"1" and "2". These are text values which have specific meaning.

Let say, "1" means "employed" and "2" means "unemployed". I would like to convert the "1" and "2" values to "employed" and "unemployed" for the feild in the table.

I tried using the IIF Function, but because it is used with numerical values, it doesn't work for me. I am not opposed to creating a new table to do this, just not sure how to change the values. Any assistance would be genuinely appreciated.
Jan 24 '08 #1
Share this Question
Share on Google+
5 Replies


Dököll
Expert 100+
P: 2,364
Hi

In my database, I use data that is imported XML data. One of the feilds in my table has text data defined with only two values -->"1" and "2". These are text values which have specific meaning.

Let say, "1" means "employed" and "2" means "unemployed". I would like to convert the "1" and "2" values to "employed" and "unemployed" for the feild in the table.

I tried using the IIF Function, but because it is used with numerical values, it doesn't work for me. I am not opposed to creating a new table to do this, just not sure how to change the values. Any assistance would be genuinely appreciated.
Hey ALaurie10!

Do a quick search here for Iff function or NZ. Please forgive me, I am currently in class. But I believe there are a number of posts here on this, perhaps some even by myself.

Please have a look, and stay tuned if no go or Google it:-)

Good luck!
Jan 24 '08 #2

Dököll
Expert 100+
P: 2,364
Hey ALaurie10!

Do a quick search here for Iff function or NZ. Please forgive me, I am currently in class. But I believe there are a number of posts here on this, perhaps some even by myself.

Please have a look, and stay tuned if no go or Google it:-)

Good luck!
I believe it's:

Expand|Select|Wrap|Line Numbers
  1.  
  2. IIf([YourField]= 1, "Employed", "Unemployed")
  3.  
  4.  
Might need to get rid of the quotes, try w/without.

In this case though, the 1 and the 2 would need to be in the same field

YourFiled = 1 or 2

Bye bye!
Jan 24 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
Since the 1 or 2 are text, per the OP, the syntax needs to be

IIf([YourField] = "1", "Employed", "Unemployed")

with quotes around the 1, but the question remains how to get this into the table, instead of the 1 or the 2.

One way would be to not put it in the table, but rather have it in a calculated field in a query based on the table, then use the query as a basis for forms/reports. Forms and reports should really be based on queries anyway, even if only a single table is involved. In the query you'd create a new field with something like

EmploymentStatus: IIf([YourField] = "1", "Employed", "Unemployed")

then refer to the field EmploymentStatus when you want to see Employed or Unemployed.

Linq ;0)>
Jan 24 '08 #4

P: 26
Thank you both, I am going to try both. Thanks again.
Feb 11 '08 #5

100+
P: 167
hey Laurie!

I believe the easiest way for you is to use an Update query.
Just type Update query in your help in Access and you will find explanation and examples there.

This query is supposed to select all the values from your table that match a certain criterion, and then to change those valus (text) to some other text that you put in the -Update to- row in query design.

Hope this helps
Feb 11 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.