469,290 Members | 1,887 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

VBA SQL Where statement syntax

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
10 7619
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. "WHERE (([Securities].[Name]) Like '" & Me.namecombo  & "*" & "')"
  2.  
Mary
Dec 16 '06 #2
NeoPa
32,173 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
Now Ade

It would have worked anyway

Mary
I know :(.
I was being fussy - but it was fun :)
Dec 16 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
thank you guys for this great "cursus"
I feel like a genius at VB now :)
Dec 20 '06 #10
MMcCarthy
14,534 Expert Mod 8TB
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.

Similar topics

47 posts views Thread by Andrey Tatarinov | last post: by
6 posts views Thread by HeadScratcher | last post: by
13 posts views Thread by eman1000 | last post: by
37 posts views Thread by Steven Bethard | last post: by
18 posts views Thread by Steven Bethard | last post: by
28 posts views Thread by Steven Bethard | last post: by
7 posts views Thread by Steven Bethard | last post: by
23 posts views Thread by florian.loitsch | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.