• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Importing to Access 2010

I have imported a .dbf file to Access 2010.  The table has a number of Yes/No fields.  They are all set to Yes in Access after the import regardless of how they were set in the original .dbf file.  How do I import and have them retain their correct values?
0
jacobblack555
Asked:
jacobblack555
  • 6
  • 6
1 Solution
 
jacobblack555Author Commented:
I need to modify my explanation of the records that have been imported to Access.  They have all imported as -1 not Yes.  My mistake.  Sorry!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Some app will use other values from what Access likes.

In *MS Access*, No is 0 and Yes is *any other value*.


So I suspect that there are no "0" values in the original data
;-)

Import the table as is (with the original values)
(Do not change the DataType to Yes No, yet!)
Then create a translation table that says what value is true or False in the original data with what Access needs.
0
 
Jeffrey CoachmanMIS LiasonCommented:
..Sorry...

Then run an update query like this to change the values to what Access needs to see...

For example.
If in the source data 1=True and 2 =false, you would run a query like this:

UPDATE YourTable SET YourTable.YourYesNoField = IIf([YourYesNoField ]=2,0,-1);

So here I am locking onto the "defined" value in Access ( 0=False), and anything else will be true (-1)

Make sense?

;-)

JeffCoachman
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
Sorry again.

Then verify that the new (updated) table values are eithe -1 or 0 (in step with the original Yes/No values.)
the you can go ahead and change the dataType of the field to Yes/No.

When you see the scary message about "Some data May Be Lost", ...simply click "Yes"

All that error means is that if there were still any numbers other than 0 or -1 now in the table, (which there should not be...) that would be converted to:
0=No
*any other Number=-1

;-)

jeffCoachman
0
 
jacobblack555Author Commented:
I'm sorry I've neglected this answer for so long.  Have been unavoidable tied up.
Thank you for answering.  I am fairly new to Access and don't understand some things you are talking about.  First - there are both YES & NO values in the original table.
When you say "Do not change the DataType to Yes No, yet!", do you mean leave that field in Access as a text field until everything has imported?  Will it then import as Yes/No?  What do you mean by a "translation table?"  I do not understand that term.  Thank you for your help!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Import the DBF file to Excel and to Text.
Post them here
0
 
jacobblack555Author Commented:
After reading the explanation a couple more times, it is making sense.  I think I understand what to do now.  It may be a while until I can get back to this project to try it, so I will close this question now and if I have more trouble, I'll post another question.  Thank you for your help!
0
 
jacobblack555Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for jacobblack555's comment #37811604

for the following reason:

I understand the instructions of the solution even if I haven't had the chance to work on it.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<After reading the explanation a couple more times, it is making sense.  I think I understand what to do now. >
If the explanation you are referring to is my post, then shouldn't you accept my post as the solution?

I don't mind waiting for you to fully evaluate it...

JeffCoachman
0
 
jacobblack555Author Commented:
Sorry, Jeff!  I clicked the wrong button.  This was my first venture into Experts Exchange.
0
 
jacobblack555Author Commented:
Jeff - If I wanted to contact you later, do I just come back to this thread?
0
 
Jeffrey CoachmanMIS LiasonCommented:
yep.

My contact info is also in my profile
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now