String search in a table's field 1

[MilinPCH]

Hi,
Is there a way I can do a string search in a field of a table using a prompt box? Ex...I have a field in my table of names in a format like, "Mr. and Mrs. George Bush." I would like to set up a query that pops up a box that prompts the user to enter the name/word they want to search for. The user could enter "Bush" and it would find the record. on another occassion, the user could enter "George" and find the same record.

Thanks,
MilinPCH

 

[jagilman]

dim sql as string
dim searchFor as string
searchFor = inputbox("Enter a search word","Find This&quot
sql = "Select * From YourTable Where YourField Like '" & searchFor & "';"

Now the sql value contains what the user is looking for.

Some if statements and error management is required however in the event the user selects cancel from the input box.

Let me know if this helps.

John

John A. Gilman
gms@uslink.net

 

[MilinPCH]

Thanks jagilman, but I don't understand how to incorporate this code into my query. I know a little about VB, and I created this code as a module and then as a macro pointing to the module, but how do I have the query Call it?

THanks!

 

[PaulF]

place this in the Query's Criteria Row for the field with the data

Like "*" & [enter Name] & "*"


PaulF

 

[MilinPCH]

Perfect! Thank you PaulF!

 

[jagilman]

PaulF has the fastest, down and dirty way to get the job done!
However I use a read only view of my table data in a form where the user can double click on any column and get a popup form that allows the search criteria to be entered. By using vb you can make one block of code respond to a search on any of the available fields.

John

John A. Gilman
gms@uslink.net