Please help designing Access Database

D

Dean & Monika

Hi people,
I am wondering if anyone can help me. I am trying to design an Access
database which has 7 fields in the table. I do not have a good knowledge of
Access though and am stuck on one particular thing, a "search" for words
within the field.

The database consists of 7 fields:
Name of Company text
Website hyperlink
Name of Contact text
Contact number text/phone number
Closing date text (need only for month closing)
Keywords Text
Description Memo.

What I need to do is run a search from a form where you enter the word that
you are after and access will display all records with that word in it and
give you either a report on the screen or a print out of all available
records. For example, if you are looking for "yellow," type it in the form
and it will search the keywords and description fields and give you a list
for it to print or view.

I have been trying online for ages to find a solution but cannot, so out of
desperation I am asking here. Any response would be appreciated.
Regards,
Dean

PS if you want to email me, try either reversing the attached email or try:
m c d s o l @ G O A W A Y S P A M m s n . c o m without the spaces and
goawayspam.
 
E

Eric Cardenas

|
| Hi people,
| I am wondering if anyone can help me. I am trying to design an Access
| database which has 7 fields in the table. I do not have a good knowledge
of
| Access though and am stuck on one particular thing, a "search" for words
| within the field.
|
| The database consists of 7 fields:
| Name of Company text
| Website hyperlink
| Name of Contact text
| Contact number text/phone number
| Closing date text (need only for month closing)
| Keywords Text
| Description Memo.
|
| What I need to do is run a search from a form where you enter the word
that
| you are after and access will display all records with that word in it and
| give you either a report on the screen or a print out of all available
| records. For example, if you are looking for "yellow," type it in the
form
| and it will search the keywords and description fields and give you a list
| for it to print or view.
------------
Hi Dean,

You need to design a "query by form":

1. The output will be based on a parameterized query where the parameter is
from your unbound form.
2. The criteria in the keywords and description would look like:

Like "*" & Forms!MyUnboundForm!MyUnboundText & "*"

The above uses the Like clause to perform a rudimentary full text search.
The Like clause allows you to search on a particular phrase in any position
within the text field.

Hope this helps,
 
D

Dean & Monika

Hi Eric,
Thanks for getting back to me! I don't mean to sound rude or anything (ie
stupid), but I don't actually understand your reply. I am very
access-illiterate and only know a few basics. Would you mind explaining
what you mean in "laymans" terms? I am sorry to be a nuisance.
Regards,
Dean
 
E

Eric Cardenas

| Hi Eric,
| Thanks for getting back to me! I don't mean to sound rude or anything (ie
| stupid), but I don't actually understand your reply. I am very
| access-illiterate and only know a few basics. Would you mind explaining
| what you mean in "laymans" terms? I am sorry to be a nuisance.
| Regards,
| Dean
----------
Hi Dean,

It is I who must apologise to you for not explaining things to your level
of understanding. Unfortunately, it is difficult to explain this technique
with just mere text that usenet provides.

I would suggest that you search for other sources for examples of "query by
form." There used to be a Solutions sample database back in previous
versions of Access. I'm not sure if the current versions still have them or
if the solutions database still exists but is not installed automatically.

Sorry I wasn't too much of a help to you.
 
B

Brendan Reynolds \(MVP\)

Here's an example, Dean. Assume that your search form is called 'frmSearch',
that the text box into which you enter the text to search for is called
'txtSearch', and that this form is open, with appropriate text entered into
the 'txtSearch' text box, when you run the example query below.

Here's the SQL for the query. Don't worry if SQL is new to you, you don't
need to write it, I'll explain in a moment.

SELECT YourTableNameHere.*
FROM YourTableNameHere
WHERE (((YourTableNameHere.Keywords) Like "*" &
[Forms]![frmSearch]![txtSearch] & "*") OR ((YourTableNameHere.Description)
Like "*" & [Forms]![frmSearch]![txtSearch] & "*"));

Replace 'YourTableNameHere' with the name of your actual table, then copy
the SQL string to the clipboard. Create a new query in design view, close
the dialog that pops up without adding any tables, then select SQL View from
the view menu. Paste in the SQL string, and switch back to design view.

The query will probably make much more sense to you when you see it in
design view - it's just much quicker for me to post the solution using a SQL
statement than to try to explain, step-by-step, how to create the same query
in the design view.
 
G

Guest

Here is a simple way to handle this:
When a query is run with an unidentified variable, Access
prompts you for the value. So if you enter [Search for
what word] on the Field row of a query, a dialog box will
pop up when the query is run saying 'Search for what word
________'

Set up a query that performs the search that you want.
Then place a command button on your form that opens the
query.
 
D

Dean & Monika

Ok thank you all for replying to this, it is appreciated. I will attempt
this and let you guys know how I got on.

Thanks again!
Regards,
Dean
 
Top