Using ComboBox in sql statement

aparnag

Registered User.
Local time
Today, 18:53
Joined
Apr 19, 2010
Messages
27
I have a form which has 3 cascading combo boxes and a command button(spread).
What it should do is, when some one presses Spread, an sql query should dump all the data in excel. Tat is working,
But I want to sql statement where clause be based on combo box values.

sqltxt = "Select * From tnwtd Where tnwtd.SBU=" & me.combo1.value

But it keeps throwing error 3462 or missing operand error. I think the sql syntax for VBA is wrong.
Can anyone help me out..
Greatly appreciate help.

Thanks,
AG
 
If SBU is text it needs delimiters:

sqltxt = "Select * From tnwtd Where tnwtd.SBU=" & Chr(34) & me.combo1.value & Chr(34)
 
Yeah SBU column in my table contains text datatype. So what should I use a " or ' nothing seems to work.

what i am trying to do is get sql to search the query based on the combo values. So, i ve added it as where SBU = combo1.value

my code is
sqltxt = "Select * from WtdSpreadtoFTPbyTerm " & _
" WHERE [SBU] =" & Me.Combo47.Value

Can you please rectify this
Would greatly appreciate that..
Thanks,
AG
 
Yeah SBU column in my table contains text datatype. So what should I use a " or ' nothing seems to work.
You don't put it in the table. Read my response again. I gave you the EXACT code to use.
 
Thankssss a million.. IT worked like a charm.. Thankss once again
 
Hi.. Sorry to bother u again, but I was trying to add additional where clause in my sql query, which now looks like

sqltxt = "Select * from WtdSpreadtoFTPbyTerm " & _
" WHERE [SBU] =" & Chr(34) & Me.Combo47.Value & Chr(34) & _
" OR " & " WHERE [Region] =" & Chr(34) & Me.Combo41.Value & Chr(34) & _
" OR " & " WHERE [Officer Name] =" & Chr(34) & Me.Combo51.Value & Chr(34)

It is giving missing operand error.. Can you please help me in this...
Greatly appreciate your help..
Thanksss
AG
 
Only ***1*** WHERE in a Select statement
Try
Code:
sqltxt = "Select * from WtdSpreadtoFTPbyTerm " & _
" WHERE [SBU] =" & Chr(34) & Me.Combo47.Value & Chr(34) & _
" OR   [Region] =" & Chr(34) & Me.Combo41.Value & Chr(34) & _
" OR  [Officer Name] =" & Chr(34) & Me.Combo51.Value & Chr(34)
 
Yeah I after I wrote the msg, l looked up online and rectified it..
I am complete newbie.. So making all silly mistakes.. :)
But thanks so much for people like u who reply in these forums.. or else i would have never figured it out.. Do u know any good turorial which is exclusive for Form designing, esp. combo boxes which are linked to sql query..

Thanks,
AG
 
Can't say I know something that specific. But, you can check out the Free Tutorials listed in my signature and see if they help.
 
Hi All,

I was trying to add one more combo box in the form and changed the query

sqltxt = "Select * from WtdSpreadtoFTPbyTerm " & _
" WHERE [SBU] =" & Chr(34) & Me.Combo47.Value & Chr(34) & _
" OR [Region] =" & Chr(34) & Me.Combo41.Value & Chr(34) & _
" OR [Product] =" & Chr(34) & Me.Combo59.Value & Chr(34) & _
" OR [Officer Name] =" & Chr(34) & Me.Combo51.Value & Chr(34)

It gives error 3601 "Too few parameters. Expected 1."

I just added product in where clause. I dont understand why it is giving tat error. Any help will be appreciated.

Thanks,
Appz
 
Thanks,, But my product combo box had a text size, so i increased the Chr to 50 and it worked, just after I put the post :)..
But now it is giving error 3075. and the " quotes in the product field is missing, it adds 2 in front while processing.
[Product] = 2Buslineequity2
rather than [Product]= "buslineequity"
Product is a text field. and the syntax is same as the rest the why when proceesing, it is not putting the data in ""
 
add a

Debug.Print Me.Combo59.Value

and look in the Immediate Window to make sure it is returning what it is you think it should have.

Oh, and you really should rename your controls to something more useful than Combo59, etc. It will help you and others down the road when, in 2 years, you have to revisit it and you can know immediately what it is. Good naming is one key to making code self documenting.
 
Yeah It is.. Is is printing Buslineequity but when it running the sql, it is putting 2Buslineequity2 not "Buslineequity". The syntax is the same for all, so it does not make sense to me.
 

Users who are viewing this thread

Back
Top Bottom