Boolean output in SQL statement
Posted on 2013-01-29
I am writing an SQL statement that I need to output 2 fields as boolean. The data is currently "text" in one field and "long" in the other.
I wrote a function that I thought would do it using the SQL statement like so...
Select ID, JobNo, Owner, ConverttoBoolean([BookedIn]) AS [On Site].....
and the function....
Public Function ConverttoBoolean(Str As Variant) As Boolean
If IsNull(Str) Then
ConverttoBoolean = False
ConverttoBoolean = True
Once the recordset is created, I am testing the fields types using....
For Each fld In recDetail.Fields
If fld.Type = 1
and I know the type is coming out as 3 (Integer) rather than 1 (Boolean).
I've also tries True/False and 0/-1 in my function but get the same result. I'm open to suggestions for a different approach but am dynamically building forms based on the field type so really need that to work!
Hope it makes sense,