ADO where function syntax

[Paul Sansom]

I have an ADO connection to and table and i cannot see what is wrong with the reference to a sheet cell
If i change br for a simple text e.g. 'h' the filter works correctly.
What is the correct syntax for a refence to a cell on a sheet (Output). FYI Output is not the sheet where the results are shown
Many Thanks Paul

Private Sub byBranchRef()
'   Filter by branch ref
    Dim myADO As New clsADO
    myADO.Connect ThisWorkbook.FullName
    
    Dim br As String
    br = Output.Range("d5")
    
    myADO.RunQuery "Select Photonumber,BranchRef,Comments From [Photos$]" _
        & " Where BranchRef = br ", shResults
End Sub

 

[Joe4]

If br is a variable, it needs to be outside of the double-quotes, or else it is treated as literal text.

Also, If the BranchRef field is indeed a string, you may need text qualifiers around it.
If single quotes are valid text qualifiers (Iike 'h' seems to suggest), then try:

    myADO.RunQuery "Select Photonumber,BranchRef,Comments From [Photos$]" _
        & " Where BranchRef = '" & br & "'", shResults

 

[Paul Sansom]

If br is a variable, it needs to be outside of the double-quotes, or else it is treated as literal text.

Also, If the BranchRef field is indeed a string, you may need text qualifiers around it.
If single quotes are valid text qualifiers (Iike 'h' seems to suggest), then try:

    myADO.RunQuery "Select Photonumber,BranchRef,Comments From [Photos$]" _
        & " Where BranchRef = '" & br & "'", shResults

Many thanks, sorry for slow response , works well

 

[Joe4]

You are welcome.

Glad I was able to help!