Convert this and win ..... well nothing 1

[awalli6i]

Hey all

I am trying to write a prog that converts a MS Access 97 DB to SQL Server 7 DB. However I need to conevert some of the data from one trype to another. To do this i have used the convert function within my SQL string (I am using VB6 by the way). How ever when I try to convert TRUE/FALSE to Binary it won't do it !

Here is the string :



Update PRemp_Employee set PRempSupPrintingRDO = " & " convert(Binary(10), '" & temp & "' ) where PRempEmployeeCode = ('" & code & "')

Can any1 help me !

Andrew

 

[rajeevnandanmishra]

Hi Andrew,
I am not confirmed if you want to convert true/false to BIT or BINARY. And moreover, what is the datatype of your TRUE/FALSE value. Is it numeric/ logical or character.
Though following are the strings, with assumption that 'true/false' is a character column. (Modify the comparison, if it is not)
To BINARY-->
"UPDATE PRemp_Employee SET PRempSupPrintingRDO = " & IIF(temp="TRUE","CONVERT(binary,1)","CONVERT(binary,0)&quot & "WHERE PRempEmployeeCode = ('" & code & "') "
To BIT-->
"UPDATE PRemp_Employee SET PRempSupPrintingRDO = " & IIF(temp="TRUE","1","0&quot & "WHERE PRempEmployeeCode = ('" & code & "') "


Let me know if it works!

 

[awalli6i]

Hey

Thanks for that...

After having a close look I was wrong... I am sorry, had support calls all over the place. The field is YES/NO and I need to convert it to BINARY....

Any advice ?

 

[tlbroadbent]

Yes/No column is really just a number column, Yes=-1 and No=0.

"Update PRemp_Employee set PRempSupPrintingRDO = " & IIF(temp,"1","0&quot & " Where PRempEmployeeCode = '" & code & "'"

OR

"Update PRemp_Employee set PRempSupPrintingRDO = " & CStr(Abs(temp)) & " Where PRempEmployeeCode = '" & code & "'"
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.