Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Boolean output in SQL statement

Hi Experts,

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
Else
ConverttoBoolean = True
End If

End Function

Once the recordset is created,  I am testing the fields types using....

For Each fld In recDetail.Fields

If fld.Type = 1
...code....
End if
Next fld

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,
Norb.
0
Norbert2000
Asked:
Norbert2000
3 Solutions
 
mbizupCommented:
Your recordset type is checking the data type in the table's design.

However your function ConverttoBoolean is only returning a value... not changing your field type.

If you check the VALUE of your your field using your recordset code, you will see the 0 or -1 that is returned by your function, stored as an integer.

Your code has no effect on the datatype in your table's design - so you would either have to predefine the desired field as a Yes/No field, use an alter table SQL statement to change the design, or come up with an alternative way of handling the output based on the value in the field rather than the type of the field.
0
 
Gustav BrockCIOCommented:
Why reinvent the wheel:

Select ID, JobNo, Owner, Not IsNull([BookedIn]) AS [On Site].....

or even:

Select ID, JobNo, Owner, ([BookedIn] Is Not Null) AS [On Site].....

/gustav
0
 
Dale FyeCommented:
If the point is that you want to be able to drag these fields onto your form and have them create checkboxes rather than textboxes, that is not going to happen.  You will need to add a checkbox control to your form and then the field as the checkboxes control source.  BTW, that control will not be editable.

But both your query (with the function call) and gustav's much cleaner query (without the function call) will return a numeric value (0, -1), which can be interpreted by the checkbox as a boolean value.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Norbert2000Author Commented:
mbizup,

When you say predefine the field as Yes/No - do you mean in the table structure or is there a way of telling the SQL query?

Gustav,

Much appreciated - but same result (although much neater way of getting there!)

Norb.
0
 
Norbert2000Author Commented:
I'm actually using a TList control and wanted a checkbox based on the field type.   Being editable is not an issue.

As sometimes happens with Experts, I may not have the solution I wanted but I at least understand the problem and see great value in that.

Many Thanks All - if it's OK with the 3 of you I'll split the points.

Norb.
0
 
mbizupCommented:
-->> When you say predefine the field as Yes/No - do you mean in the table structure

Yes.  Have a target field for this defined as boolean in your table's design.

You CAN atlter field types (design) through SQL statements using ALTER TABLE, but I'm not sure of the exact syntax, it would be run as a second query and I would opt for any of the alternatives we have presented in these comments here first.
0
 
mbizupCommented:
-->> Many Thanks All - if it's OK with the 3 of you I'll split the points.

Whatever you find most helpful.  I think all of us have been around long enough to not rip each other to shreds over a few points :)
0
 
Norbert2000Author Commented:
Just as an addition, I ended up renaming the fields I wanted with CB_ and testing for this to present the data in the way I needed.

Not sure that will help anyone else but solved my problem!

Norb.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now