Convert Access String to SQL Server BIT Data Type

I've got a table in MS Access 2000 which contains text strings of "Y" or "N" (for yes and no, obviously).

I'm trying to move that data using ADO or a append query, into a MS SQL Server 7 table,  where the data type is formatted as BIT.

Anyone know of the conversion or formatting code to do this?

I know how to move it, I just need to convert the data before or as I move it.

I tried CBool and CByte but got data type conversion errors each time..
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.

Use this from your append query.

BitValueFromText: iif([YourFieldName]="Y",1,0)

The IIf is a function that evaluates an expresion and returns the second parameter if the expression is true and the third if the expression is false.

On SQL Server, True is represented as 1 and false as 0.
IIf is *very* slow, this will work faster:

INSERT INTO LinkedSQLTable (BitField)
  SELECT Abs(YesNoField = "Y") FROM AccessTable;

You can perform this query directly from Access (and probably want to add some more fields to it) if you make an ODBC-link to the SQL-Server table. No need to code at all.

EvanLAuthor Commented:
Hmm.. I'll try both.  I'm only working with <300 records at a time, so IIf may work fine.  A big "duuhh" on the IIf to, Trygve.  I use that all the time in my reports.  Didn't even think of it for this purpose..
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EvanLAuthor Commented:

The IIf function is working well (naturally), but the values are coming out in the SQL Server tables as -1 = Yes and 0 = No.

Any ideas how to change that?  I always thought it was supposed to be 1=Y and 0=No.
If you have linked the tables into access they will appear as -1 for True and 0 for False. These are Access' version of boolean numeric values.

A common, general test for true and false is
False: Field = 0
True: Field <> 0

Because of the above version differences. I think it shold be possible to register any number except 0 into the field and get True values in return, not 100% sure though.

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
The SQL Server Bit field is probably mapped to an Access Yes/No field. This is the case if you use an Access ODBC link to SQL Server.
    Access Yes/No fields contain VBA Boolean values, and True in VBA is kept in memory as the value FFFF (Hex) which is -1. This is a conversion Access does for you.
    The SQL Server Bit-field can only contain a 1 or a 0. If you try to get a -1 in there in any way you will get an error. You can check this with a query from you enterprise manager, directly to the SQL Server table without Access as in-between.

EvanLAuthor Commented:

Thanks for the help and the answer.  The fact that the 'Yes' value in MS Access comes out as -1 is not a big deal. In my queries and reports, I can convert that easily to 'Y' or '1' depending on my needs.  The main thing, is that the correct values are stored in the SQL table.  Heck, I don't care if they were 'L' for 'Yes' and '7' for 'No' as long as they were consistent.


Thanks for the info.  You are right.  The value shows up in SQL Server as '1' for 'Yes' and '0' for 'No'.  Please look in the general MS Access area for some points for helping me out.

Thanks gents!
On your reports and forms you have the format property of the controls which can be used to make the values come out as you want. Typical for boolean fields are Yes/No and True/False, but you can insert your own version if you like.
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.