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

Posted on 2008-01-24
Medium Priority
Last Modified: 2013-12-25
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

Open in new window

Question by:rfgraham
LVL 38

Expert Comment

ID: 20738401
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")
LVL 77

Accepted Solution

GrahamSkan earned 2000 total points
ID: 20738667
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 Comment

ID: 20738879
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?


Expert Comment

ID: 20739227
'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)
      'You can customize
      If .Available Then
        sInfo = sInfo & "1" 'Set To Yes or Whatever you want.
        sInfo = sInfo & "0" 'Set To Yes or Whatever you want.
      End If
    Print #2, sInfo
End With

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month3 days, 11 hours left to enroll

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question