473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
13 1494
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
What SQL are you using? Are you using Access for this?
Mar 19 '07 #6
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
12,516 Expert Mod 8TB
Ask them specifically what SQL Server they're using.
Mar 20 '07 #8
Ask them specifically what SQL Server they're using.

Yes. We are using SQL Main. thanks.
Mar 20 '07 #9
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
Let us know how that works out for you.
Mar 20 '07 #12
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
14,534 Expert Mod 8TB
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

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

Similar topics

4
by: Adrian Charteris | last post by:
Hi I'm currently trying to use a lookup table for converting one xml doc to another using a XSLT transformation. Ideally I would like my first xml doc to be converted to my second xml doc below. ...
1
by: Sophie Bradshaw | last post by:
i was wondering if anyone could possibly help me! i have a ms access database, and in one of the tables, one of the fields is a lookup column, with several possible values. what i want to do is...
7
by: Marco Simone | last post by:
Hi, What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2. Is this good design of database? ...
4
by: MJ | last post by:
One of my tables has a lookup field in it. It looks up a part number but stores it as the part's name (bad idea). How can I change it back to storing the part number as a part number without...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
1
by: Dan | last post by:
I am using an expression in Forms to take several fields of data and convert them to a block of text. For one of the fields set-up as a combo box, it is based on a Lookup of a separate table to...
4
by: keri | last post by:
Hi everybody, I'm very new to access so please treat me with kid gloves! (i'm hopeless with code and macros but enjoying learning). I am loving what it is capable of though, and i'm sure I...
11
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in...
1
by: roveagh1 | last post by:
Hi I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.