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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!
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.
Jeffrey CoachmanMIS LiasonCommented:

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?


Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

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:
*any other Number=-1



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
Jeffrey CoachmanMIS LiasonCommented:
Import the DBF file to Excel and to Text.
Post them here
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!
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.
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...

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

My contact info is also in my profile
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.