Improve company productivity with a Business Account.Sign Up

x
?
Solved

Boolean output in SQL statement

Posted on 2013-01-29
8
Medium Priority
?
450 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 668 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 53

Assisted Solution

by:Gustav Brock
Gustav Brock earned 664 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 50

Accepted Solution

by:
Dale Fye earned 668 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

607 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