We help IT Professionals succeed at work.

Need to change checkbox Yes/No value to an integer 1 or 0 In recordset usning VB6

rfgraham
rfgraham asked
on
Through a SQL select I have 7 fields I am getting data from, two of the fields are boolean, these two field values need to be change from True/False to 1 or 0. The data is coming from an Access table.
Is there a way of changing these data values in my recordset?

See attached code (Done in VB6)

Thanks for your help
I need to change rs2(5) and rs2(6) to a 1 or 0 depending on the boolean value.
________________________________________________________________________
While Not rs2.EOF
'Fields are stored in array
finalData2 = rs2(0) & "," & rs2(1) & "," & rs2(2) & "," & rs2(3) & "," & Chr(34) & rs2(4) & Chr(34) & "," & rs2(5) & "," & rs2(6)
'Print finalData2
Print #2, finalData2
rs2.MoveNext
Wend

Open in new window

Comment
Watch Question

Commented:
The IIF function can evaluate it in one line as long as neither field is Null.  (I assume you want 1 if true and 0 if false.)

finalData2 = rs2(0) & "," & rs2(1) & "," & rs2(2) & "," & rs2(3) & "," & Chr(34) & rs2(4) & Chr(34) & "," & IIF(rs2(5), "1", "0") & "," & IIF(rs2(6), "1", "0")
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
Perhaps you can just take the negative of the results:

finalData2 = rs2(0) & "," & rs2(1) & "," & rs2(2) & "," & rs2(3) & "," & Chr(34) & rs2(4) & Chr(34) & "," & -rs2(5) & "," & -rs2(6)

Author

Commented:
Well, before I checked EE for any expert comments I came up with this and it worked fine:

finalData2 = rs2(0) & "," & rs2(1) & "," & rs2(2) & "," & rs2(3) & "," & Chr(34) & rs2(4) & Chr(34) & "," & Abs(rs2(5)) & "," & Abs(rs2(6))

I also ran PaulHews but it didn't work.
I ran Graham Skan's and it work just fine. Will my solution cause me any problems if I go with it?

Thanks

Commented:
'Structure of Database
' rs.FirstName
' rs.LastName
' rs.Available  Yes/No

Dim sInfo As String

With rsDatabase
    Do While Not .EOF
      sInfo = "" 'Clear For New Record
      sInfo = sInfo & Str(.FirstName)
      sInfo = sInfo & ","
      sInfo = sInfo & Str(.LastName)
      sInfo = sInfo & ","
     
      'You can treat a Boolean as yes/no true/false with If Statement
      'You can pass whatever is in the database:
      sInfo = Str(.Available)
     
      'Or
      'You can customize
      If .Available Then
        'True
        sInfo = sInfo & "1" 'Set To Yes or Whatever you want.
      Else
        'False
        sInfo = sInfo & "0" 'Set To Yes or Whatever you want.
      End If
    Print #2, sInfo
    .MoveNext
    Wend
End With

Explore More ContentExplore courses, solutions, and other research materials related to this topic.