Solved

Boolean output in SQL statement

Posted on 2013-01-29
8
417 Views
Last Modified: 2013-01-29
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
Comment
Question by:Norbert2000
8 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 38830493
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 38830545
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
ID: 38830722
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:Norbert2000
ID: 38830813
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
 
LVL 1

Author Comment

by:Norbert2000
ID: 38830824
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38830830
-->> 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
 
LVL 61

Expert Comment

by:mbizup
ID: 38830841
-->> 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
 
LVL 1

Author Comment

by:Norbert2000
ID: 38830965
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

825 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