Solved

Boolean output in SQL statement

Posted on 2013-01-29
8
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 51

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 48

Accepted Solution

by:
Dale Fye 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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