textbox in sql query 3

[obulldog27]

what is the syntax if I want to substitute ChemicalName in the query for a textbox so that user can define his own search field.

Private Sub Command1_Click()
SearchCriteria = find_box.Text

If SearchCriteria = "" Then
  MsgBox "Enter a Criteria"
Else
With compchartado
.RecordSource = "SELECT * FROM Comp_Chart WHERE ChemicalName like '" & SearchCriteria & "%'"
.Refresh
End With
End If
End Sub

 

[gmmastros]

.RecordSource = "SELECT * FROM Comp_Chart WHERE " & txtSearchField.Text & " like '" & SearchCriteria & "%'"

Also, you should use a replace statment on the search criteria so....

Change the first line to...
SearchCriteria = Replace(find_box.Text, "'", "''")

If someone entered a word with an apostrophe, your query would fail.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Well suppose you have a textbox named txtSearchField ....

.RecordSource = "SELECT * FROM Comp_Chart WHERE " & txtSearchField.Text & " like '" & SearchCriteria & "%'"

Or did I not understand the question?

 

[jebenson]

Basically, do it the same way you do the criteria:

Private Sub Command1_Click()
SearchCriteria = find_box.Text
DearchField = field_box.Text

If SearchCriteria = "" Then
  MsgBox "Enter a Criteria"
Else
With compchartado
.RecordSource = "SELECT * FROM Comp_Chart WHERE " & SearchField & " like '" & SearchCriteria & "%'"
.Refresh
End With
End If
End Sub

I would suggest a listbox or combobox where the user can just select a field name, rather than a text box where the user might (probably will...no, DEFINITELY will) enter a non-existent field name. Also, you need to have some sort of decision code (if, select, etc.) to configure the SQL command properly for different data types. The code above will only work if any field that can be selected is a character or text field. If a numeric or date field is supplied, the SQL code will fail as it is currently written.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Sheco]

woah!

Your post wasnt up yet when I mashed the reply button but somehow we both decided to name the control txtSearchField

spooky

 

[gmmastros]

Great minds think alike, right?

If that were true, there would only be about 50 patents in the U.S. patent office.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jebenson]

Great minds think alike, right?

If that were true, there would only be about 50 patents in the U.S. patent office

Looks like somebody read today's Dilbert!


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[gmmastros]

Every morning. Not much gets past you jebenson.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[obulldog27]

Thanks worked great