If Statement Ignores Boolean Values in Access VBA

[gr8dane]

I have a form with several controls that I'm using to filter its records. To make it simple, let's say the controls are cboCat, chkA, chkB and cmdSearch. In reality there are several more combos and everything worked perfectly until I added the check boxes.

This is the simplified code I've got:

In the form's module:

Private Sub cmdSearch_Click()
  Dim lngCatID As Long, blnA As Boolean, blnB As Boolean

  If (Me!cboCat = 0) And (Me!chkA = False) And (Me!chkB = False) Then
    MsgBox "Please select something to search by."
  Else
    lngCatID = Nz(Me!cboCat)
    blnA = Me!chkA, 
    blnB = Me!chkB
    
    strFilter = GetFilterString(lngCatID, blnA, blnB)

    Me.Filter = strFilter
    Me.FilterOn = True
  End If
End Sub

In a standard module:

Public Function GetFilterString(ByVal lngCatID As Long, _
                                ByVal blnA As Boolean, _
                                ByVal blnB As Boolean) As Variant
  Dim strFilter As String

MsgBox blnA & " " & blnB

  If (lngCatID > 0) And (blnA = False) And (blnB = False) Then     'Case 1
    strFilter = "CatID = " & lngCatID
  ElseIf (lngCatID = 0) And (blnA = True) And (blnB = False) Then  'Case 2
    strFilter = "A = " & blnA
  ElseIf (lngCatID = 0) And (blnA = False) And (blnB = True) Then  'Case 3
    strFilter = "B = " & blnB
  End If

MsgBox strFilter
    
  GetFilterString = strFilter
End Function

In all three cases, the first MsgBox in the function gives the correct values and the second MsgBox shows the correct filter string in Case 1. But in Cases 2 & 3, nothing appears in the second MsgBox and no filtering happens on the form. I've also tried the "...And blnA And Not blnB..." style with the same result. What am I doing wrong?

[June7]

I tested your function by calling it from Immediate Window with inputs that should return appropriate message. It works for all 3 cases.

When would lngCatID be null?

Fields are named "A" and "B"?

Could provide db for analysis. Follow instructions at bottom of my post.

[gr8dane]

Thanks for responding so quickly. I forgot to say that these are all unbound controls. I set the default value for cboCat (and all the other combos) to 0. lngCatID would only have a different value if something had been selected. No, there are no fields named A and B; I was just simplifying my code for this post.

Like I said, this all worked perfectly before I tried including the checkboxes in the code. And it still does as long as I don't choose one of those. There must something wrong with the way I'm checking their values in the if statement.

[Micron]

Maybe I've missed something, but if just one of these tests are not True,

If (Me!cboCat = 0) And (Me!chkA = False) And (Me!chkB = False)
MsgBox "Please select something to search by."

Then the Else portion is evaluated:
Else
lngCatID = Nz(Me!cboCat)
blnA = Me!chkA,
blnB = Me!chkB

Yet chkA or chkB may be True, False or Null?

Perhaps you need to step through the code after making various selections and check if the variables/references evaluate to what you expect, or if the code flow is as you expect.

[gr8dane]

Yet chkA or chkB may be True, False or Null?

Well, not exactly. The checkboxes currently have a default value of 0, so it should never be Null, should it? I think I originally set the defaults to False, but I'm not sure what the correct value should be. I still get confused when it comes to Null, 0, "", etc. ;-(

[June7]

Checkbox not bound to Yes/No field can be 0, -1, Null unless TripleState property is set to No. Setting DefaultValue property will populate with a value but user could select Null state unless TripleState property is set to No. Even with set as No, unbound checkbox will have Null if there is no DefaultValue when form first opens.

[gr8dane]

Maybe I've missed something, but if just one of these tests are not True,

If (Me!cboCat = 0) And (Me!chkA = False) And (Me!chkB = False)
MsgBox "Please select something to search by."

Then the Else portion is evaluated:

That part works correctly.

Perhaps you need to step through the code after making various selections and check if the variables/references evaluate to what you expect, or if the code flow is as you expect.

That's what the MsgBoxes are there for.

[gr8dane]

Checkbox not bound to Yes/No field can be 0, -1, Null unless TripleState property is set to No. Setting DefaultValue property will populate with a value but user could select Null state unless TripleState property is set to No. Even with set as No, unbound checkbox will have Null if there is no DefaultValue when form first opens.

TripleState is set to No. Default value is currently 0, although I originally set it to False.

[davegri]

That's what the MsgBoxes are there for.

You've verified the values of the checkboxes, but not the combobox.
Perhaps the combobox has more than one column; the default is cboCat.column(0)

[gr8dane]

You've verified the values of the checkboxes, but not the combobox.

The combo has always been correct, so I didn't bother verifying it.

[Micron]

That's what the MsgBoxes are there for.

Not from what I see. If the line I'm referring to does not evaluate to True, then what does blnA =Me!chkA evaluate to? The msgbox line does not reveal that to us.
BTW, you might want to check out the difference between using bang (!) vs dot (.)
! will not reveal compile errors but will reveal run time errors (when it is perhaps too late). I never use it unless I'm writing code for recordsets, which seems to be the only time it's necessary.

[gr8dane]

If the line I'm referring to does not evaluate to True, then what does blnA =Me!chkA evaluate to?

I'm assuming it evaluates to either True or False, depending on whether or not it's been selected (the first MsgBox in the function confirms that). If that's not the case, please explain.

BTW, you might want to check out the difference between using bang (!) vs dot (.)

I've done a lot of searching on that topic, and everyone gives a different answer. I've settled on putting a bang between a form and a control on it and a dot between a control and a property. Earlier today I accidentally put a dot where I usually put a bang and the line went red until I changed it, so I must be on the right track there.

[davegri]

The combo has always been correct, so I didn't bother verifying it.

Ah, but saying so doesn't make it so.

Your symptoms are exactly as described If cboCat is > 0 for Case 2 or Case 3

[Welshgasman]

That part works correctly.


That's what the MsgBoxes are there for.

Well they are not solvinjg your problem though, are they?

A simple walk of the code would find the issue in a heartbeat, but you seem reluctant to do so.
As June7 mentioned it works for her, so the values are not what you expect.

[Micron]

Earlier today I accidentally put a dot where I usually put a bang and the line went red until I changed it, so I must be on the right track there.

I don't think that is reliable. Try this: choose a procedure that refers to a form control. Make sure your code is compiled and saved. Edit the form control so that it is wrong (e.g. Me!txtCustomer changed to Me!txtCust) and compile. There should not be an issue. Now change it to Me.txtCust and compile and you'll raise an error. In the first case, you will not raise an error until that line of code executes (if ever). In the second case, you won't be able to complete a project until you fix the error, which is better than releasing faulty code I'd say.

I don't know what you read on this subject but I've read lots of stuff where people don't know what they're talking about so I can relate to what you said.
Simply put, the bang operator provides late bound access to the default property of the thing that follows it. Being late bound means it is not evaluated until the code line is executed.

The checkboxes currently have a default value of 0, so it should never be Null,

You said that the checkboxes are unbound. IIRC, unbound controls will ignore default value property settings. Even with default values, when the form is opened are these controls not grey (not selected, and not white)? If they are, their values are Null, not whatever your default property value is.