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

How do I lookup the corresponding value of a numerically coded field?

P: 7
Hi,
I have a number field coded as 0 and 1 (representing male, female). I would like to convert this integer into text (i.e., 0=male, 1=female). Could you help? Please be specific.
I am using Window XP and dob.
Many thanks in advance.
Fuzhong
Mar 19 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Hi,
I have a number field coded as 0 and 1 (representing male, female). I would like to convert this integer into text (i.e., 0=male, 1=female). Could you help? Please be specific.
I am using Window XP and dob.
Many thanks in advance.
Fuzhong
What's dob?

Sure we can help.
Expand|Select|Wrap|Line Numbers
  1. DLookup("[Field Name]", "[Table Name]", "[Code Field Name] = " & [Coded Value])
This is as specific as I can get given the information. It may not even be the best solution depending on where you want to use it.
Mar 19 '07 #2

P: 7
What's dob?

Sure we can help.
Expand|Select|Wrap|Line Numbers
  1. DLookup("[Field Name]", "[Table Name]", "[Code Field Name] = " & [Coded Value])
This is as specific as I can get given the information. It may not even be the best solution depending on where you want to use it.

Sorry that I did not make it clear.
The name of the table is Subject which has a field named Sgender which I would like to convert the number in this field into a new field (e.g., SgenderN). We use SQL. I am not an expereinced user. If you could tell how I can do this in SQL that would be great. Many thanks again.
fuzhong
Mar 19 '07 #3

Rabbit
Expert Mod 10K+
P: 12,365
This will work if your SQL has an If function:
Expand|Select|Wrap|Line Numbers
  1. (iif(Subject.SGender = 0, "Male", "Female")) As SGenderN
Mar 19 '07 #4

P: 7
This will work if your SQL has an If function:
Expand|Select|Wrap|Line Numbers
  1. (iif(Subject.SGender = 0, "Male", "Female")) As SGenderN
thanks.

I am working this off a query. I cut and pasted the code iif(Subject.SGender = 0, "Male", "Female")) into the Column (in the Sgender row) and put SGender as Alias. I recevied the following message:
ADO error: 'iff' is not a recognized function name. Did I do something wroing? Could you let me exactly how this code should be implemented? many thanks again. fuzhong
Mar 19 '07 #5

Rabbit
Expert Mod 10K+
P: 12,365
What SQL are you using? Are you using Access for this?
Mar 19 '07 #6

P: 7
What SQL are you using? Are you using Access for this?
Sorry for the confusion. I had to ask our IT people about this. We do use Access (as a front-end, and SQL server as a back-end). That's all I can tell.

would this help? thanks. fuzhong
Mar 20 '07 #7

Rabbit
Expert Mod 10K+
P: 12,365
Ask them specifically what SQL Server they're using.
Mar 20 '07 #8

P: 7
Ask them specifically what SQL Server they're using.

Yes. We are using SQL Main. thanks.
Mar 20 '07 #9

Rabbit
Expert Mod 10K+
P: 12,365
I googled SQL Main and I couldn't find a SQL Server by that name.

Have you tried making a second table with the gender codes and then inner joining them?
Mar 20 '07 #10

P: 7
I googled SQL Main and I couldn't find a SQL Server by that name.

Have you tried making a second table with the gender codes and then inner joining them?
I will try your second proposal. thank you. fuzhong
Mar 20 '07 #11

Rabbit
Expert Mod 10K+
P: 12,365
Let us know how that works out for you.
Mar 20 '07 #12

P: 7
Let us know how that works out for you.
not sure would be appropriate to ask a different question but I'm asking anyway...

I have a table that was made using a stored procedure (make-table query). Data from this table was pulled from mutiple queries. Now, I need to update cases (or subjects) in this table (tblfuzhongtemp) from the original source query (QryFuzhongTemp). I tried the append query but keep getting cases doubled (see below).

INSERT INTO fuzhongl.tblfuzhongtemp
(BlockGroupNumber, BlockID, ParticipantID, ANickName, [Full Name], Sgender, Sdob, BAddress, BCity, BZip, [Home Phone], Aphoneo2, E_Mail,
RecruiterName, Incentive, Transportation, MNote, CheckNum, Rrecruitdate, Rassdate, Scomment, Rassplace, SAssessment, PDateScheduled, Place,
PDateAssessed, PAssessor, PNote, SAppend)
SELECT BlockGroupNumber, BlockID, ParticipantID, ANickName, [Full Name], Sgender, Sdob, BAddress, BCity, BZip, [Home Phone], Aphoneo2, E_Mail,
RecruiterName, Incentive, Transportation, MNote, CheckNum, Rrecruitdate, Rassdate, Scomment, Rassplace, SAssessment, PDateScheduled, Place,
PDateAssessed, PAssessor, PNote, SAppend
FROM dbo.QryFuzhongTemp

Could you let me know whether I should do something else to avoid the duplicates? Would this infomraiton sufficient?

again, mank thanks.
fuzhong
Mar 20 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Fuzhongh

Can you post a new question as it makes it easier for everyone to follow the separate issues.

Mary
Mar 22 '07 #14

Post your reply

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