pass combo and textbox values as parameters in sql

[Guest]

Trying to design a filter using vba. On a userForm I have a combobox w/ a
list of values I've typed in which correspond to certain fields in a table. I
also have a textbox into which the user can type an appropriate value. (e.g.,
one of the combobox's entries is Date Requested. When they select this item
from the box, I want the user to be able to enter a date into the textbox
(called searchVal), and press a command button to generate an SQL Select From
Where statement. I have the following code, which generates a run-time error
(3075 - missing operator) Any ideas where I'm going wrong w/ this syntax?
Thanks in advance.

Dim strSQL As String
strSQL = "Select * From Letters "

strSQL = strSQL & "Where (" & Me.Combo158.Column(0) & ") = """ &
Me.SearchVal & """;"

Me.RecordSource = strSQL

 

[Nikos Yannacopoulos]

Assuming the field name retrieved from the combo is correct, and the field
is text, the problem is in the quotes in your Where clause, around
Me.Searchval. Try:

strSQL = strSQL & "Where (" & Me.Combo158.Column(0) & ") = '" &
Me.SearchVal & "';"

(the whole expression in one line, in case it gets wrapped in the posting).

HTH,
Nikos

 

[Brian]

Trying to design a filter using vba. On a userForm I have a combobox w/ a
list of values I've typed in which correspond to certain fields in a table. I
also have a textbox into which the user can type an appropriate value. (e.g.,
one of the combobox's entries is Date Requested. When they select this item
from the box, I want the user to be able to enter a date into the textbox
(called searchVal), and press a command button to generate an SQL Select From
Where statement. I have the following code, which generates a run-time error
(3075 - missing operator) Any ideas where I'm going wrong w/ this syntax?
Thanks in advance.

Dim strSQL As String
strSQL = "Select * From Letters "

strSQL = strSQL & "Where (" & Me.Combo158.Column(0) & ") = """ &
Me.SearchVal & """;"

Me.RecordSource = strSQL

You've given an example of a field name (Date Requested) which has a space
in it, therefore you will need to use square brackets in the query i.e.

strSQL = strSQL & "Where ([" & Me.Combo158.Column(0) & "]) = """ &
Me.SearchVal & """;"

 

[Guest]

Thanks Nikos. Your suggestion worked! I'm so new to SQL - puzzled by the
single and double quote shuffle. Thanks again.

 

[Guest]

Thanks Brian - wasn't the problem - I mis-typed Request Date in my post - in
my code and in db was one word, no space. Thanks though I learned something.

Trying to design a filter using vba. On a userForm I have a combobox w/ a
list of values I've typed in which correspond to certain fields in a table. I
also have a textbox into which the user can type an appropriate value. (e.g.,
one of the combobox's entries is Date Requested. When they select this item
from the box, I want the user to be able to enter a date into the textbox
(called searchVal), and press a command button to generate an SQL Select From
Where statement. I have the following code, which generates a run-time error
(3075 - missing operator) Any ideas where I'm going wrong w/ this syntax?
Thanks in advance.

Dim strSQL As String
strSQL = "Select * From Letters "

strSQL = strSQL & "Where (" & Me.Combo158.Column(0) & ") = """ &
Me.SearchVal & """;"

Me.RecordSource = strSQL

You've given an example of a field name (Date Requested) which has a space
in it, therefore you will need to use square brackets in the query i.e.

strSQL = strSQL & "Where ([" & Me.Combo158.Column(0) & "]) = """ &
Me.SearchVal & """;"