Solved

Boolean output in SQL statement

Posted on 2013-01-29
8
425 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 50

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

713 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