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

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

LVL 1
weirdturnedproAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clarkscottCommented:
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
clarkscottCommented:
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
clarkscottCommented:
and even more.....  that's assuming that the field will be 0 or -1/1  and not NULL.
Scott C
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim P.Commented:
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
weirdturnedproAuthor Commented:
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
Jim P.Commented:
>> 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
clarkscottCommented:
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
Jim P.Commented:
<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
weirdturnedproAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clarkscottCommented:
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
Jim P.Commented:
>> 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
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.