MS Access Database Search Form

[sinistapenguin]

Hi All

I'm fairly new to access, but I have built ASP pages to interface with an Access back end in the past.

The problem is I just upgraded my PC and now have XP Home so I can't locally host a website.

My problem should be fairly simple to you guys though:

I have a database that consists of 1 table! it has about 5 columns in it, but loads of rows.

All I want to do is to have a form or data access page into which you type what kind of record you want to find.

I then want the database to return records that match the specified criteria.

eg. I have a table cataloguing images that I can use. I want to find all pictures of 'Bathrooms', so I want to type 'Bathrooms' into a form and have the DB return a list of images of Bathrooms!

The only thing is I want it to return any image that has Bathroom in any of several columns. The word bathroom may appear in the Category column, or the 'Description' column.

I would imagine this is not beyond the realms of possibility. Ideally I would like it to be a data access page as I have some HTML programming experience and I can make it look prettier!!

Anyone got any tips for me?

Thanks

Ben

 

[BSman]

Set up a form to display your data. I'll call it frmDisplayData.

On the bottom (or top, if you prefer) part of the form place a text box (I'll call it txtSearchFor) where the user can type in a word/words/phrase/part of a word to be found.

Either place a button to start the search, or just automatically start the "search" as soon as the user presses enter.

The data source for the form will be a query which will be something like the following (where I'm using 3 text columns as an example):

qselMatchingData =
SELECT * FROM MyData WHERE (Col1 Like ""*" & form!frmDisplayData.txtLookFor & "*'") OR
(Col2 Like ""*" & form!frmDisplayData.txtLookFor & "*'") OR
(Col3 Like ""*" & form!frmDisplayData.txtLookFor & "*'")

To actually display the matching records, all you need is the following statement:

me.requery

 

[BSman]

After I pressed the submit post button, I realized I had typos. Here's the more correct SQL statement:

SELECT * FROM MyData WHERE (Col1 Like "'*" & form!frmDisplayData.txtLookFor & "*'") OR
(Col2 Like "'*" & form!frmDisplayData.txtLookFor & "*'") OR
(Col3 Like "'*" & form!frmDisplayData.txtLookFor & "*'")

You may have to play around a bit more with this. The basic idea is that the SQL statement should end up looking something like this if the search text is "bath":

SELECT * FROM MyData WHERE (Col1 Like '*bath*') OR
(Col2 Like '*bath*') OR (Col2 Like '*bath*')