By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

VBA SQL Where statement syntax

P: 10
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:
Expand|Select|Wrap|Line Numbers
  1. "WHERE (([name] Like " Me.namecombo " & "*" & ")"
"Namecombo" is the name of a combo box

PLease help debug!
Thanks!!!
Dec 16 '06 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. "WHERE (([Securities].[Name]) Like '" & Me.namecombo  & "*" & "')"
  2.  
Mary
Dec 16 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
Maybe Evans has a point about all that whiskey!
Try :
Expand|Select|Wrap|Line Numbers
  1. "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.
Dec 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Maybe Evans has a point about all that whiskey!
Try :
Expand|Select|Wrap|Line Numbers
  1. "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
Dec 16 '06 #4

NeoPa
Expert Mod 15k+
P: 31,186
Now Ade

It would have worked anyway

Mary
I know :(.
I was being fussy - but it was fun :)
Dec 16 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I know :(.
I was being fussy - but it was fun :)
go get ready to go out and stop checking up on me. ;)

Mary
Dec 16 '06 #6

P: 10
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Setnamecbo()
  2.     Dim strcboSQL As String
  3.     Dim strWhere As String
  4.     Dim itemVal As Variant
  5.     Dim accListcbo() As Variant
  6.     Dim lngLen As Long
  7.     Dim msg As String
  8.  
  9.  
  10.     strcboSQL = ""
  11. [namecombo].SetFocus
  12.  
  13. strcboSQL = "SELECT [Name], [Coupon rate], [Maturity DD/MM], [Curr], [Type], [ISIN], [Bloomberg]" & vbCrLf & _
  14.             "FROM [Securities]" & vbCrLf & _
  15.             "WHERE ([Name] Like '" & "*" & Me.namecombo.Text & "*')"
  16.         With Me.Typelist
  17.         For Each itemVal In Me.Typelist.ItemsSelected
  18.         If Not IsNull(itemVal) Then
  19.         strWhere = strWhere & """" & .ItemData(itemVal) & """, "
  20.         End If
  21.         Next
  22.         End With
  23.         lngLen = Len(strWhere) - 2 'Without trailing comma and space.
  24.         If lngLen > 0 Then
  25.         strWhere = "[type] IN (" & Left$(strWhere, lngLen) & ")"
  26.         strcboSQL = strcboSQL & " AND " & strWhere
  27.         End If
  28.  
  29.  
  30.     strcboSQL = (strcboSQL & vbCrLf & "ORDER BY [name];")
  31.     'msg = MsgBox(strcboSQL, vbInformation)
  32.  
  33.  
  34.     Me.namecombo.RowSource = strcboSQL
  35.  
  36. End Sub
  37.  
  38.  
Dec 17 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Setnamecbo()
  2.     Dim strcboSQL As String
  3.     Dim strWhere As String
  4.     Dim itemVal As Variant
  5.     Dim accListcbo() As Variant
  6.     Dim lngLen As Long
  7.     Dim msg As String
  8.  
  9.    strcboSQL = ""
  10. [namecombo].SetFocus
  11.  
  12. strcboSQL = "SELECT [Name], [Coupon rate], [Maturity DD/MM], [Curr], [Type], [ISIN], [Bloomberg]" & _
  13.             "FROM [Securities]" & _
  14.             "WHERE ([Name] Like '*" & Me.namecombo.Text & "*')"
  15.         With Me.Typelist
  16.         For Each itemVal In Me.Typelist.ItemsSelected
  17.    If Not IsNull(itemVal) Then
  18.       strWhere = strWhere & "'" & .ItemData(itemVal) & "', "
  19.    End If
  20.         Next
  21.         End With
  22.         lngLen = Len(strWhere) - 2 'Without trailing comma and space.
  23.         If lngLen > 0 Then
  24.         strWhere = "[type] IN (" & Left$(strWhere, lngLen) & ")"
  25.         strcboSQL = strcboSQL & " AND " & strWhere
  26.         End If
  27.  
  28.         strcboSQL = (strcboSQL & "ORDER BY [name];")
  29.     'msg = MsgBox(strcboSQL, vbInformation)
  30.  
  31.                  Me.namecombo.RowSource = strcboSQL
  32.  
  33. End Sub
  34.  
  35.  
Mary
Dec 17 '06 #8

NeoPa
Expert Mod 15k+
P: 31,186
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'.
Dec 17 '06 #9

P: 10
thank you guys for this great "cursus"
I feel like a genius at VB now :)
Dec 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 20 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.