weirdturnedpro
asked on
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).
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and even more..... that's assuming that the field will be 0 or -1/1 and not NULL.
Scott C
Scott C
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'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.
ASKER
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?
Is there any way to use a similar IF statement in a query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
<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>
>> 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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
>> 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.
I've done it with delivered apps. Its just getting a consistent response -- and learning not to trust the VBA defaults.
Glad to be of assistance. May all your days get brighter and brighter.
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