VBA SQL Where statement syntax

[Forest14]

Hello,

I have been trying to solve this issue for long and need help from you guys!
Here is the point:
Looking to get the following SQL into VBA:

SELECT Securities.Name, Securities.ISIN
FROM Securities
WHERE (((Securities.Name) Like [Forms]![Securities Search tool]![namecombo] & "*"))

Apparently my WHERE condition syntax is wrong:

"WHERE (([name] Like " Me.namecombo " & "*" & ")"

"Namecombo" is the name of a combo box

PLease help debug!
Thanks!!!

[MMcCarthy]

Try this ...

"WHERE (([Securities].[Name]) Like '" & Me.namecombo  & "*" & "')"
 

Mary

[NeoPa]

Maybe Evans has a point about all that whiskey!
Try :

"WHERE ([Name] Like '" & Me.namecombo  & "*')"

(a small slip in truth.)
You can add [Securities]. before [Name] too if you like. It is optional in this context.

[MMcCarthy]

Maybe Evans has a point about all that whiskey!
Try :

"WHERE ([Name] Like '" & Me.namecombo  & "*')"

(a small slip in truth.)
You can add [Securities]. before [Name] too if you like. It is optional in this context.

Now Ade

It would have worked anyway

Mary

[NeoPa]

Now Ade

It would have worked anyway

Mary

I know :(.
I was being fussy - but it was fun :)

[MMcCarthy]

I know :(.
I was being fussy - but it was fun :)

go get ready to go out and stop checking up on me. ;)

Mary

[Forest14]

Thanks for help.
Now you can enjoy the week-end.
Here is the code I used to requery the dropdown list of a combo while typing:

Private Sub Setnamecbo()
    Dim strcboSQL As String
    Dim strWhere As String
    Dim itemVal As Variant
    Dim accListcbo() As Variant
    Dim lngLen As Long
    Dim msg As String
 
 
    strcboSQL = ""
[namecombo].SetFocus
 
strcboSQL = "SELECT [Name], [Coupon rate], [Maturity DD/MM], [Curr], [Type], [ISIN], [Bloomberg]" & vbCrLf & _
            "FROM [Securities]" & vbCrLf & _
            "WHERE ([Name] Like '" & "*" & Me.namecombo.Text & "*')"
        With Me.Typelist
        For Each itemVal In Me.Typelist.ItemsSelected
        If Not IsNull(itemVal) Then
        strWhere = strWhere & """" & .ItemData(itemVal) & """, "
        End If
        Next
        End With
        lngLen = Len(strWhere) - 2 'Without trailing comma and space.
        If lngLen > 0 Then
        strWhere = "[type] IN (" & Left$(strWhere, lngLen) & ")"
        strcboSQL = strcboSQL & " AND " & strWhere
        End If
 
 
    strcboSQL = (strcboSQL & vbCrLf & "ORDER BY [name];")
    'msg = MsgBox(strcboSQL, vbInformation)
 
 
    Me.namecombo.RowSource = strcboSQL
 
End Sub
 
 

[MMcCarthy]

I'm glad you got it working. Now I'm going to take a leaf out of Adrians book and be a little pedantic. :D

You don't need to use the vbCrLf command in any of the query.

Private Sub Setnamecbo()
    Dim strcboSQL As String
    Dim strWhere As String
    Dim itemVal As Variant
    Dim accListcbo() As Variant
    Dim lngLen As Long
    Dim msg As String
 
   strcboSQL = ""
[namecombo].SetFocus
 
strcboSQL = "SELECT [Name], [Coupon rate], [Maturity DD/MM], [Curr], [Type], [ISIN], [Bloomberg]" & _
            "FROM [Securities]" & _
            "WHERE ([Name] Like '*" & Me.namecombo.Text & "*')"
        With Me.Typelist
        For Each itemVal In Me.Typelist.ItemsSelected
   If Not IsNull(itemVal) Then
      strWhere = strWhere & "'" & .ItemData(itemVal) & "', "
   End If
        Next
        End With
        lngLen = Len(strWhere) - 2 'Without trailing comma and space.
        If lngLen > 0 Then
        strWhere = "[type] IN (" & Left$(strWhere, lngLen) & ")"
        strcboSQL = strcboSQL & " AND " & strWhere
        End If
 
        strcboSQL = (strcboSQL & "ORDER BY [name];")
    'msg = MsgBox(strcboSQL, vbInformation)
 
                 Me.namecombo.RowSource = strcboSQL
 
End Sub
 
 

Mary

[NeoPa]

This sounds like I'm posting for the sake of it but I'm really not.
I've seen this happen before ...
Mary is right about the VbCrLf but you should also understand that while SQL interprets any white-space (Characters which don't produce any characters printed but simply reposition where the next character would be printed) equally, and multiple white-space characters qualify the same as a single one, An example of a white-space character should be used to separate the items previously separated by the VbCrLf.
That's the long version for anyone who's curious, the short version is 'Don't forget to replace any VbCrLfs with tab or space characters if you remove them'.

[Forest14]

thank you guys for this great "cursus"
I feel like a genius at VB now :)

[MMcCarthy]

thank you guys for this great "cursus"
I feel like a genius at VB now :)

Try answering a few simple questions here. You may surprise yourself.

Mary