469,945 Members | 2,389 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

Search String in Database Tables

Hello Everyone,

I am new to MS Access and although I have created several different
databases I lack the VB knowledge to code a search function. I am
turning to your expertise for assistance. I am using MS Access 2003.

This is what I am looking for:

A text field for the user to enter the search string or keyword.

Once the user enters the string or a partial string (i.e. *house* or
hou* or *use), the search funtion locates all the records in ALL
database tables that match the string. It is OK for the user to enter
*str or *str* or str* to locate the string (keyword) in all the fields
of ALL database tables.

For output, a new form comes up showing the entire row in the database
table(s) that match the string.

The user moves the mouse pointer to the record, shown on the output
form, to be updated or deleted.

The user updates the record and presses a save button on the form to
store the data back in the table(s). The form also has a delete button
to get rid of the record.

It is OK to illustrate the string search functionality using one
database table; instead of searching multiple tables in the database,
if it is easier to code.

Your expert advice is greatly appreciated.

Thank you,

Edwin

Sep 12 '06 #1
1 11177

at*******@gmail.com wrote:
Hello Everyone,

I am new to MS Access and although I have created several different
databases I lack the VB knowledge to code a search function. I am
turning to your expertise for assistance. I am using MS Access 2003.

This is what I am looking for:

A text field for the user to enter the search string or keyword.

Once the user enters the string or a partial string (i.e. *house* or
hou* or *use), the search funtion locates all the records in ALL
database tables that match the string. It is OK for the user to enter
*str or *str* or str* to locate the string (keyword) in all the fields
of ALL database tables.

For output, a new form comes up showing the entire row in the database
table(s) that match the string.

The user moves the mouse pointer to the record, shown on the output
form, to be updated or deleted.

The user updates the record and presses a save button on the form to
store the data back in the table(s). The form also has a delete button
to get rid of the record.

It is OK to illustrate the string search functionality using one
database table; instead of searching multiple tables in the database,
if it is easier to code.

Your expert advice is greatly appreciated.

Thank you,

Edwin
Sounds like a dodgy design if you're storing the same information in
different fields in different tables (well, unless it's a foreign key).
it's possible to do what you're asking, but if you wanted to union the
results together for all the tables (so you'd get one big list of
matches), the result would not be updateable. I guess you could create
listbox or something and add the number of records matching in each
table...

the basic syntax is

SELECT *
FROM [someTable]
WHERE [somefield] LIKE "*" & Forms!MyForm!txtSearchString & "*"

Sep 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ryan | last post: by
9 posts views Thread by Christopher Koh | last post: by
1 post views Thread by Matthew Curiale | last post: by
1 post views Thread by ratnakarp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.