Search Form Textbox Adding characters

[tfoulkes]

? Search Form Textbox Adding characters ?

Hi,

I've got a query that does a search on the text from textbox on a form.

The query runs fine when you run it directly, asking for the text field [forms]![search_form]![wordInput].[Text]
and finding specific results on what you type, or more if you add wildcards.

The problem is that on the form I have a button (created with the wizard) which runs the query.
I fill in the text box and click it but it finds no results
This seems to be putting in extra characters to the string.

Ultimately, I want to add wilcards to the SQL so that it'll find any results that match anywhere in the specified field.

TIA

Tim

 

[dcx693]

on the form I have a button (created with the wizard) which runs the query

What's the code behind that button?

 

[tfoulkes]

I've put no code behind it

it's just the button wizard...

Should I code an event procedure?

 

[dcx693]

Just trying to figure out why it's different if the query is run directly (from the database window, I assume) versus running it off a button on a form.

The button wizard created some code to run the query when you click that button on the form. Just wondering what code it created.

 

[tfoulkes]

What I've done....

...is put this code behind the button:

Private Sub cmdSearch_Click()
If IsNull(Me.SearchTextBox) Then
MsgBox "You have not entered any Name to Search.", vbExclamation, "Title"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM data WHERE [Page Path]Like '*" & Me.SearchTextBox & "*' ORDER BY [Page Path];"

End With
End Sub

I just find it easier to use queries for getting the results and exporting them.

This pulls back everything, but doesn't show all the fields in the list box?

Thanks for the help though, still a bit of a puzzler why it didn't work before.

Timbo

 

[dcx693]

Confused

It sounds like you're running a parameter query (i.e., one that asks for your input), however you are looking for the results to display inside a listbox on your form, which is actually taking it's source from a SQL statement that gets records from a query named "data".

Please post the SQL text for the "data" query. If you're not sure how to do that, go to the query in design view, go to the View menu and choose SQL View. Copy and paste the text into a post.

 

[tfoulkes]

Re: Confused

Please post the SQL text for the "data" query

I'm no longer running the query. I've deleted it and am using the on_click procedure of a button to search the table "data" with the code above.

Sorry for the confusion

It all works fine now... I've upped the column count of the list box so that it shows all the data and have included an order by clause.

Thanks for the help though
I know where to come next time I stubble!

Tim

 

[hangglide]

Re: What I've done....

...is put this code behind the button:

Private Sub cmdSearch_Click()
If IsNull(Me.SearchTextBox) Then
MsgBox "You have not entered any Name to Search.", vbExclamation, "Title"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM data WHERE [Page Path]Like '*" & Me.SearchTextBox & "*' ORDER BY [Page Path];"

End With
End Sub

I just find it easier to use queries for getting the results and exporting them.

This pulls back everything, but doesn't show all the fields in the list box?

Thanks for the help though, still a bit of a puzzler why it didn't work before.

Timbo

tfoulkes,
I am trying to implement your code but I am not sure what you mean by [Page Path]. I understand the rest (I think).

Leo

 

[hangglide]

Oh, I see now... That is the column name in the WHERE clause. Do you know how to search multiple columns using a simillar query?

TIA
Leo

 

[tfoulkes]

Hmmm!
good question...

in my example:
.RowSource = "SELECT * FROM data WHERE [Page Path]Like '*" & Me.SearchTextBox & "*' ORDER BY [Page Path];"

change it to:
.RowSource = "SELECT * FROM data WHERE [Page Path] OR [Date] OR [Name] Like '*" & Me.SearchTextBox & "*' ORDER BY [Page Path];"

Just add the other columns you want to search with OR statements.

And that should fix it!

HTH

 

[hangglide]

tfoulkes,

Thanks for your reply, That is what I want to do. I used something very similar to your code to modify the recordsource of my continous form. It works great (eg it performs the search and displays only the matching records) when I include only one column in the WHERE clause of the SQL statement:

For example:

SELECT * FROM queryAllPermits WHERE ApplicantName Like '*Jim*' ORDER BY permitID

Returns 3 out of 600 records (great)

However,

SELECT * FROM queryAllPermits WHERE ApplicantName OR County Like '*Jim*' ORDER BY permitID

Returns 600 out of 600 records (eg all of them) :-(

Any suggestions?


Thanks again,

Leo

 

[Mile-O]

You have not specified a criteria for ApplicantName meaning that it evaluates as TRUE. Due to the SQL using an OR statement you are returning all ApplicantName's that are True (any value but 0) or those Counties Like *Jim*

Therefore, you need to set your criteria for ApplcaintName field too:

SELECT * FROM queryAllPermits WHERE (ApplicantName = '*Jim*' OR County Like '*Jim*') ORDER BY permitID;

 

[tfoulkes]

WHAT HE SAID!!!

 

[hangglide]

Great, Thank you very much. I forgot that from my database class. Hmmm...

Leo

 

[Mile-O]

Although I wouldn't "hard-code" the Jim part into a query.

I'd put a textbox on a form and reference that. If the user wanted to type in Jim then they could but this one query would satisfy all entries...

SELECT * FROM queryAllPermits WHERE (ApplicantName = ""*[Forms]![MyForm]![MyTextbox]*"" OR County Like ""*[Forms]![MyForm]![MyTextbox]*"") ORDER BY permitID;

I also changed the inverted commas to quotation marks and doubled them up. It'll fix silly searches.

 

[hangglide]

Gotcha. I hadent planned on hard-coding in the search terms, it was more for the example, but I appreciate the help on the syntax, etc.... That should clean up my code a little too. Every little tip helps.

Thanks,

Leo