Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I compare a bit value in VBA to use in an IF statement for an Access Report?

Posted on 2008-11-18
12
Medium Priority
?
300 Views
Last Modified: 2012-05-05
I have an Access 2003 frontend connecting to an SQL database backend on our network. My employer needed a check box added to mark customers that are to receive only exact counts on their shipments (no overage). I successfully added the bit field (ExactCount) to the Customer table (dbo_Cust). I was also able to successfully display the bit field in the query attached to the final report.

Within the report is an unbound value (txtShipQty1). I checked the VBA module for the report and modified the code to match my alterations. I've tried using several variations to compare the string (= 0, <> -1, <> 1, = False, <> True, etc.) but it doesn't seem to be recognizing the value and making the comparison the way I intended.

I've spent about 4 hours on this and I'm fresh out of ideas (willpower).
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Rem Exact Count Modifier Check
  If ExactCount <> -1 Then
    If Qty < 500 Then
      txtShipQty1 = Qty + 25
    ElseIf Qty < 2500 Then
      txtShipQty1 = Qty + 50
    Else
      txtShipQty1 = Qty * 1.02
    End If
  Else
    txtShipQty1 = Qty
  End If
End Sub

Open in new window

0
Comment
Question by:weirdturnedpro
  • 5
  • 5
  • 2
12 Comments
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 800 total points
ID: 22988155
In the reports Detail section (I'm assuming that's where this checkbox field is) right-click in the left-side margin on the small gray box.  In the FORMAT event......

if YourField <> 0 then
      me.YourCheckBox = true
else
      me.YourCheckBox = false
end if


Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 22988173
PS...  Access stores a TRUE as -1.  SQL is 1.
But they both use 0 for false.
Therefore, when checking a True/False status, alway check for ZERO (false) and if not zero, then it's True.

Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 22988190
and even more.....  that's assuming that the field will be 0 or -1/1  and not NULL.
Scott C
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 38

Expert Comment

by:Jim P.
ID: 22988991
Just throwing in my $0.02....

I've always had a problem using the SQL Bit data type with an Access/JET/VBA front-end because of the +1 vs. -1 issue.

I've always used the smallint (-32,767 to 32,767) in SQL as its the smallest integer that can hold a negative.
0
 
LVL 1

Author Comment

by:weirdturnedpro
ID: 22989338
I just finished converting the values to 'tinyint'; it doesn't seem to have solved my problem. I haven't had any issues with this when I develop queries, but the VBA aspect admittedly has me a little puzzled. Having it looked over by peers and trying several different permutations doesn't seem to be making any difference either.

Is there any way to use a similar IF statement in a query?
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 800 total points
ID: 22989530
>> I just finished converting the values to 'tinyint';

The tinyint is 0 to 255. No negative numbers.


>> Is there any way to use a similar IF statement in a query?

Iif(ExactCount = 0, <False Statement>, <True Statement>)

0
 
LVL 20

Expert Comment

by:clarkscott
ID: 22993137
Just for conversation.....
You will probably run into this again (perhaps many times).  The solution isn't always to change the table structure. Hence... you may as well deal with it now.
As I stated earlier... it always works for all booleans if you check for zero or not zero.
No matter if the backend uses -1 or 1 for true ... ZERO ALWAYS = FALSE.

:-)

Scott C
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22994301
<Conversation>

>> No matter if the backend uses -1 or 1 for true ... ZERO ALWAYS = FALSE.

What about nulls? That is the catch for Boolean operators. If the value is null, is it true or false. And sometimes I don't know means I don't know. :-p

That is one to test for. Or my preference is to always give a default value for a field.
</Conversation>
0
 
LVL 1

Accepted Solution

by:
weirdturnedpro earned 0 total points
ID: 22998458
Yeah - I thought of that already and updated the fields with zeroes and a single record to test with 1. I've already tried altering the VBA to look for =0 instead of <>1 or <>-1 (actually that's what I tried initially - I just sent of the code amid attempting to change my strategies).

It was my understanding that Access translates this difference in regard to bit values since when I viewed the table in Access it showed 0 and -1 (as opposed to 0 and 1 [MS SQL]). Either way I feel that I've ran through the enumerations to hopefully identify another potential issue.

Is there any other information that I can furnish that might provide better insight into this? (i.e. table schema, attached queries, etc)
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 23001753
jimpen
You're absolutely correct in your opinion that these booleans SHOULD be defaulted to true or false during table design.

And to be thorough, we should test for null in any criteria - boolean or text.

But I still stand firm in my suggestion to learn to deal with booleans across the different backends (versus changing the boolean to something else) because booleans are not going away.  
Now, if you have your own tables that you've structured and you want to eliminate booleans... go ahead - it's your stuff.
But now everyone has this luxery to redesign backends simply because they don't know how to deal with booleans consistantly. ( :-)   -  I'm not trying to sound critical :-)  )

Just my thoughts......

Scott C



0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23003507
>> my suggestion to learn to deal with booleans across the different backends

I've done it with delivered apps. Its just getting a consistent response -- and learning not to trust the VBA defaults.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23816252
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Implementing simple internal controls in the Microsoft Access application.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

564 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