SQL Where clause help

[dhartford]

Here is my code and error message

error message: No given value for one or more required parameters



......
strSQL = "Select distinct [order date] FROM [Sheet1$]" & _
"where Company =" & lstCompanyFilter.Value
........


lstCompanyFilter.Value is the company name selected in 1st list box. The statement extract the data from spreadsheet and feed to a 2nd list box.

It works fine if I comment out the where clause.

Thanks in advance.

[CreganTur]

Whenever you use SQL in VBA and you refer to the value of an object or variable, then you must wrap the object/variable with specific symbols that tell SQL what data type it's looking at.

If you're using numbers, then no symbol is needed. If String, then you wrap with single quotes. If dates, then you use pound (#) sign.

Try this:

strSQL = "Select distinct [order date] FROM [Sheet1$]" & _
"where Company ='" & Me.lstCompanyFilter.Value & "'"

[Demosthine]

Hey there.

One other thing you need to remember is that when you are using a line continuation like this, you have to make sure you have spaces added properly. If you remove the line continuation, as you (both) have it written, would read:


strSQL = "Select distinct [order date] FROM [Sheet1$]where Company ='" & Me.lstCompanyFilter.Value & "'"



Good luck.
Scott

[dhartford]

Try this:

strSQL = "Select distinct [order date] FROM [Sheet1$]" & _
"where Company ='" & Me.lstCompanyFilter.Value & "'"


CreganTur,

You are right, it works. Thank you very much.

I really don't understand VBA. When code works with SQL but may not work with VBA. With the SQL code above, Debug.print showes the Recordcount is 0. I'm sure it should not be which means there are order dates match the company on the spreadsheep. When I SQL company and order data seperately, both work fine. Could you think anything that may be. Here is the code


.......
strSQL = "Select [order date] FROM [Sheet1$]" & _
"where Company ='" & "lstCompanyFilter.Value'"

OrderdateRs.Open strSQL, objConn, adOpenStatic, adLockOptimistic

lstPlacementDateFilter.RowSourceType = "value list"

Debug.Print OrderdateRs.RecordCount
........



Thanks again.

[Mavyak]

You are Debug.Printing PlacedateRs's record count but you are populating the OrderdateRs recordset. Is that correct?

Also, your quotes are mis-placed around your listbox object. Reread the way the code was presented to you and mimic it identically.

[dhartford]

You are Debug.Printing PlacedateRs's record count but you are populating the OrderdateRs recordset. Is that correct?

Also, your quotes are mis-placed around your listbox object. Rererad the way the code was presented to you and mimic it identically.

Sorry, it should be:




.......
strSQL = "Select [order date] FROM [Sheet1$]" & _
"where Company ='" & "lstCompanyFilter.Value'"

OrderdateRs.Open strSQL, objConn, adOpenStatic, adLockOptimistic

lstPlacementDateFilter.RowSourceType = "value list"

Debug.Print OrderdateRs.RecordCount
........


What do you mean "your quotes are mis-placed around your listbox object"?.

Thanks a lot.

[Mavyak]

"where Company ='" & "lstCompanyFilter.Value'"
That is not how Cregan and Demosthine presented the line.

[Demosthine]

Good Morning.

What Mayvak means by your quote marks are misplaced is that you've added some compared to your prior version. Specifically, look at how my previous example has them around the lstCompanyFilter.Value.

First, again I refer you back to my first reply. You have not corrected the spacing issue based on the code snippet you have posted here. You need to add a space immediately following [Sheet1$] but before quotation. I would get rid of the line continuation if I were you.

Second, after you went back to correct your code, you now have quote marks surrounding lstCompanyFilter.Value. This is wrong. Remove these quote marks. You need to conjugate the final string identifiers after you conjugate the ListBox Value. Compare the two snippets immediately below and you should understand. You can even copy my snippet below and paste it directly into your project to fix the error.


' Your code from you last post.
strSQL = "Select [order date] FROM [Sheet1$]" & _
"where Company ='" & "lstCompanyFilter.Value'"

' My code and the way it should be written.
strSQL = "Select [order date] FROM [Sheet1$] " & _
"where Company = '" & lstCompanyFilter.Value & "'"


Let me know if you need more help.
Scott

[dhartford]

Good Morning.



' Your code from you last post.
strSQL = "Select [order date] FROM [Sheet1$]" & _
"where Company ='" & "lstCompanyFilter.Value'"

' My code and the way it should be written.
strSQL = "Select [order date] FROM [Sheet1$] " & _
"where Company = '" & lstCompanyFilter.Value & "'"


Let me know if you need more help.
Scott

Scott, I got it work. Thanks.