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

weirdturnedpro used Ask the Experts™
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
      txtShipQty1 = Qty * 1.02
    End If
    txtShipQty1 = Qty
  End If
End Sub

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
      me.YourCheckBox = false
end if

Scott C
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
and even more.....  that's assuming that the field will be 0 or -1/1  and not NULL.
Scott C
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2014

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.


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?
Most Valuable Expert 2014
>> 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>)

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
Most Valuable Expert 2014


>> 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.
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)
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

Most Valuable Expert 2014

>> 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.
Most Valuable Expert 2014

Glad to be of assistance. May all your days get brighter and brighter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial