Access Keyword Search Querry

[PeanutB7]

Hello Folks,

I am attampting to build an access database by importing excel spreadsheets with rows of record data. Some cells contain text and some strictly numbers. I am ultimately attempting to querry all cells for key words that may fall within any cell within the record. For example 'identification card' would look in all record and corresponding cells and pull up all records that meet the critera of having the words 'identification card' in any cell within the record.

Also is there a limit to how big of a spreadsheet you can import it access? I attempted to import a large excel sheet and not only did it take forever but it actually froze the data base to the point that it would no longer recognize any table within the database. Any recommnedations?

Any and all help would be greatly appreciated.

I than you in advance for your assistance,

JB

 

[dhookom]

There isn't a limit to the number of records in an Access table. There is a 255 field limit.

Your import issue may be caused by complexity of data types and possibly indexes on the table fields.

Searching through many or all fields suggests the table structure might not be normalized. If you want to search several fields at once you can concatenate the fields like:

SearchFields: [FieldA] & [FieldB] & [FieldC]

Duane MS Access MVP

 

[PeanutB7]

Duane

Thank you so much for the answer but can I ask you to elaborate on the Access querry. I am not exactly sure what you meant by the SearchFields: [FieldA] & [FieldB] & [FieldC].

Is this what is to be put in the querry column. Can it be set up like a parameter querry where the user is asked what key words are to be searched prior to running the querry.

Thanks for the help in advance

JB

 

[dhookom]

I don't use parameter prompts since I think they are a poor user interface

http://www.tek-tips.com/faqs.cfm?fid=6763.

Assuming you have a form with a text box [frmSearch].[txtSearch] you could use a query with a where clause like:

SELECT *
FROM tblYourTableNameHere
WHERE [FieldA] & [FieldB] & [FieldC] Like "*" & Forms!frmSearch!txtSearch & "*"

Duane MS Access MVP