Can you believe all you read?

[Gasman]

As most of you know, I advocate to use Google or another search engine to see if you can find a solution before asking for help.
It does not work a lot of the times I know, but I will persevere.

Anyway on another forum, someone was asking for properties of a combo box and the following link was provided.

ComboBox object (Access)

Office VBA reference topic

learn.microsoft.com

To save you going there, the pertinent code I am talking about is below.

And this code is from Microsoft? If they cannot get it right........

Private Sub cmdSearch_Click()
    Dim db As Database
    Dim qd As QueryDef
    Dim vWhere As Variant
    
    Set db = CurrentDb()
    
    On Error Resume Next
    db.QueryDefs.Delete "Query1"
    On Error GoTo 0
    
    vWhere = Null
    vWhere = vWhere & " AND [PymtTypeID]=" & Me.cboPaymentTypes
    vWhere = vWhere & " AND [RefundTypeID]=" & Me.cboRefundType
    vWhere = vWhere & " AND [RefundCDMID]=" & Me.cboRefundCDM
    vWhere = vWhere & " AND [RefundOptionID]=" & Me.cboRefundOption
    vWhere = vWhere & " AND [RefundCodeID]=" & Me.cboRefundCode
    
    If Nz(vWhere, "") = "" Then
        MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
        "Search Cancelled.", vbInformation, "Search Canceled."
        
    Else
        Set qd = db.CreateQueryDef("Query1", "SELECT * FROM tblRefundData WHERE " & _
        Mid(vWhere, 6))
        
        db.Close
        Set db = Nothing
        
        DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly
    End If
End Sub

 

[ebs17]

? IsNull(NULL & " AND [PymtTypeID] = NULL"), Nz(NULL & " AND [PymtTypeID] = NULL", "")
False         AND [PymtTypeID] = NULL

Checking whether a ComboBox returns a value goes horribly wrong.

A filter on "= NULL" never results in True, so neither does the overall filter.

Yes, it is great nonsense.

 

[isladogs]

Hi Paul
I've reported the error so it gets fixed.

 

[Gasman]

Hi Paul
I've reported the error so it gets fixed.

Thank you Colin.
Doug Steele has also reported it, as it was his link.

SO perhaps it will get corrected.

 

[Gasman]

? IsNull(NULL & " AND [PymtTypeID] = NULL"), Nz(NULL & " AND [PymtTypeID] = NULL", "")
False         AND [PymtTypeID] = NULL

Checking whether a ComboBox returns a value goes horribly wrong.

A filter on "= NULL" never results in True, so neither does the overall filter.

Yes, it is great nonsense.

No, my observation was that vWhere would never be Null ?

 

[ebs17]

vWhere = Null
vWhere = vWhere & " AND"

' Its the same as
vWhere = Null & " AND"

that vWhere would never be Null ?

That's right, you always end up in the Else branch.
However, this is also problematic if even one of the ComboBoxes does not return a <> NULL value.

The situation is different with the + operator

vWhere = Null + " AND"

? NULL + "AND", IsNull(NULL + "AND")
Null          True

Then you always end up in the if branch.

 

[NauticalGent]

Great catch Paul...

 

[Pat Hartman]

Apparently Julie No-Test is alive and well and working for Microsoft.

I reported a different error a few weeks ago and I don't do that much searching

 

[isladogs]

Apparently Julie No-Test is alive and well and working for Microsoft.

I reported a different error a few weeks ago and I don't do that much searching

Has it been fixed. If not, please supply the URL & the error & I'll report it again

 

[isladogs]

No, my observation was that vWhere would never be Null ?

Well strictly speaking it might be null . . . but it can never equal null

 

[Pat Hartman]

I didn't keep it. I'll try to find it again.

 

[gemma-the-husky]

vWhere = Null
vWhere = vWhere & " AND"

' Its the same as
vWhere = Null & " AND"

That's right, you always end up in the Else branch.
However, this is also problematic if even one of the ComboBoxes does not return a <> NULL value.

The situation is different with the + operator

vWhere = Null + " AND"

? NULL + "AND", IsNull(NULL + "AND")
Null          True

Then you always end up in the if branch.

I thought from reading the MS code was that the intention was to concatenate multiple statements all showing AND fieldname = somevalue
The final statement then uses Mid(vWhere, 6)) which presumably would remove the leading five characters, which will be " AND " leaving a useable string.

I am never 100% sure how to use & and/or + to concatenate strings where the string may include NULL., without testing.
It sounds like MS aren't either, if the example doesn't work as intended.

 

[ebs17]

The logic in the code is fundamentally flawed.
There are enough examples via forum, Google & Co, how to build a filter from several criteria, so that you should simply forget this code.

ComboBox object (Access)

The code has very little to do with the title of the article.