Search of text fields

[Design by Sue]

I will be creating a simple database with one table and about 12 fields. Two of these fields will be text fields where user will input descriptions in paragraph form. I then need to create reports based on this table. My search will need to be any or all of the words put into the search and the search has to be on all words including those in the text fields. Can this be done?

I appreciate any responses - if I need to be more clear on my question, please help me provide the information so I can get an answer. I need to prepare a quote to create this database and don't want to offer something that can't be done.

Thanks
Sue
Design by Sue

 

[vbaInet]

1 table and 12 fields really wouldn't cut it in the real world. Topics on normalisation and relational databases have been discussed in the General and Theory of Relational Databases sections so please feel free to have a browse or perform a search. Here are some links to get you started on the above:

http://r937.com/relational.html
http://www.databasedev.co.uk/database_normalization_process.html
http://support.microsoft.com/kb/283878/

Once you've covered those then you can post your table structure and we can take it from there.

The searching is possible but since we're talking paragraphs then that would be memo fields and with memo fields things can start to become slow.

Welcome to AWF by the way

 

[MSAccessRookie]

I will be creating a simple database with one table and about 12 fields. Two of these fields will be text fields where user will input descriptions in paragraph form. I then need to create reports based on this table. My search will need to be any or all of the words put into the search and the search has to be on all words including those in the text fields. Can this be done?

I appreciate any responses - if I need to be more clear on my question, please help me provide the information so I can get an answer. I need to prepare a quote to create this database and don't want to offer something that can't be done.

Thanks
Sue
Design by Sue

The Built-In Function Instr() can be used to locate a specific String within another String.

Note that what vbaInet has advised you is true. Although there will always be exceptions to any rule, one table with 12 Columns sounds more like an Excel Spreadsheet than an Access Database.

Even though your Database design seems simple, revisiting the design could make your Database more efficient. If you can show us the design of your Table, we will try to help you.

 

[Design by Sue]

I appreciate your responses - I have already discussed this 12 column database with an expert and believe it or not - that is the way it shakes down. There are no fields that have any relationships. And yes - basically it is an excel sheet!

"The Built-In Function Instr() can be used to locate a specific String within another String." Thanks for that MSAccessRookie. I would figure I could use that for several strings - yes?? (meanwhile I will search for Instr() to see what info I can find and if I find the answer I will post back here to save others of responding to something I already have the answer to.

 

[MSAccessRookie]

I appreciate your responses - I have already discussed this 12 column database with an expert and believe it or not - that is the way it shakes down. There are no fields that have any relationships. And yes - basically it is an excel sheet!

"The Built-In Function Instr() can be used to locate a specific String within another String." Thanks for that MSAccessRookie. I would figure I could use that for several strings - yes?? (meanwhile I will search for Instr() to see what info I can find and if I find the answer I will post back here to save others of responding to something I already have the answer to.

I suppose that you could test for one String at a time and then connect each Instr() with AND/OR statements to make a complete condition.

 

[Design by Sue]

Thanks again - Another thought - does anyone know of a plug in or add on of some kind that will do this. I was just going to Google and see what I could find.

 

[MSAccessRookie]

Thanks again - Another thought - does anyone know of a plug in or add on of some kind that will do this. I was just going to Google and see what I could find.

While I am always glad to assist someone with an MS Access issue, I am still not sure what you need/want to do. I believe that vbaInet was correct in the assessment of your Database. A typical Excel Spreadsheet Model tends to fall apart when implemented As-Is in MS Access. If you are able to show us the design (No Data required - Column Names and Data Types will be sufficient), then we might be able to make additional comments.

 

[boblarson]

Thanks again - Another thought - does anyone know of a plug in or add on of some kind that will do this. I was just going to Google and see what I could find.

In 14 years of working with Access databases I've never heard of one.

 

[Design by Sue]

I'm back!

Trying to simplify my problem, let's take a table of 4 fields, WEBSITE, DEADLINE, DESCRIPTION AND NOTES. Below is a sample of what would be in each field for a record:

WEBSITE www.webaddress.com

DEADLINE May 31, 2010

DESCRIPTION In celebration of Black History Month, BlackHistory.com has launched the "A Day In Black History" Scholarship contest. The contest is a great financial aid opportunity for students to win $500. The scholarship is NOT an academic scholarship - It is entirely based on an essay submission.

NOTES Submit your essay along with your name and contact info. Do not email your essay as an attachment.
If you do not follow these instructions precisely, your entry will be disregarded.
We will review all submissions and select a winner based on the quality, uniqueness, and creativity of the content in your essay. By March 31st, the winner will be awarded $500. We will not publish the winning essay, but will announce the name of the winner, and the school he/she attends. If you are the winner, the check will be written out to you, not your school.

I need to create a report based on a search of the DESCRIPTION and NOTES fields and the search needs to include a number of words to search on - up to 3 would be good, but 5 would be better. A sample search that would result in this record being included in the report would be the words "black", "essay" and "creativity". The search needs to be and/or so if some of the words are there but not all it includes the record in the resulting report.

With this is it possible for someone to tell me 1. Is this possible and 2. can it be done through a query or will it be necessary to write programming to accomplish it?

Thanks for your help

Sue Young

 

[vbaInet]

Regarding your last enquiry, the following link might throw some insight:

http://www.access-programmers.co.uk/forums/showthread.php?t=195833

 

[Design by Sue]

Thank you but I don't see how that applies.

Sue

 

[vbaInet]

That's an addition as to what you can do like counting the occurences of those words.

But here's a tutorial on building a search form, there's a sample db attached too:

http://baldyweb.com/BuildSQL.htm

If you want multiple search criteria, in the WHERE clause you use:

WHERE field1 = 'black' OR field1 = 'essay' OR field2 = 'creativity'

All that needs to be built using vba.

Hope that helps.

 

[Design by Sue]

Thanks - over night I think I realized that the counting technique would - simplified - be if the results were more than 0 then display the results.

It appears that with a little work this search IS possible at that is what I needed to know at this point. I will read through the tutorial you linked to - thanks a million.

I work with access only about once a year so everytime I go back to it there is a learning curve - though it gets smaller each time as I remember more of what I learned the previous time.

Again, thanks for the info.

Sue

 

[vbaInet]

Lots to learn but once you get to grips with the principles of vba then you will be flying

I would advise pickingn up a book or two just to get the basics of vba if you're not yet familiar.

Feel free to ask questions if you get stuck.

 

[Design by Sue]

Thanks - I have a number of good books - I posted because I needed a quick answer to how complex this would be so I could give an estimate. I didn't want to do the work BEFORE I knew it was a job. Will be back if I get the job.

Sue

 

[vbaInet]

Good luck!!!

 

[wiklendt]

Sue, this solution may also help you. it was for a different request, but sounds similar to your need. please note the final working solution is described in post #18, but you may need to read the whole thread to fully understand.