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..
LVL 1
EvanLAsked:
Who is Participating?
 
TrygveConnect With a Mentor Commented:
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.
0
 
TrygveCommented:
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.
0
 
Jeremy_DCommented:
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.

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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..
0
 
EvanLAuthor Commented:
Trygve:

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.
0
 
Jeremy_DCommented:
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.

0
 
EvanLAuthor Commented:
Trygve:

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.

Jeremy:

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!
0
 
TrygveCommented:
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.
0
All Courses

From novice to tech pro — start learning today.