searching

[Ken Snell [MVP]]

You design and create your own form that allows a user to enter the
parameters of a search, and then click a button. You have the form perform
the searches and display the results.

Easy? Depends entirely on what you want to search through, and how
experienced you are at writing queries and such.

 

[Chink!]

Hello

This may sound like a stupid question but...

I'm looking for an easy way to search a database, something which people
who've never used Access can do without getting into queries or reports.

I'm setting up an address book in Access. I'd like the user to be able to do
a "google-esque" search with a few keywords and get results. Is this
possible in Access or should I try something else?


many thanks

CH!

 

[Chink!]

That sounds like what I'm looking for

The address book will have the name of an organisation, address & contact
numbers, plus some notes. Also possible category fields which describe the
organisation. Each record would not be particularly long.
So I'd like what you described, the user sees a form, enters in the keywords
and clicks a button and gets a list of results

Care to elaborate?
I'm a fast learner....;-)

CH!

 

[Ken Snell [MVP]]

As I said, create a form that is based on a query that will return records
with the desired data. Put controls in the form's header that will be used
for entering (textbox) a search value, or for selecting (combo box) a search
parameter. Also put a command button on the form that will run this event
procedure code on its Click event:

Private Sub CommandButtonName_Click()
Me.Requery
End Sub


The query that the form uses should have criteria expressions for each field
that is to be searched similar to this:
Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null

FormName will be the name of the form that you're creating. ControlName will
be the name of the specific control that will hold the value for that field.

 

[Chink!]

Thanks for your help.
I was Ok up to "ControlName will
be the name of the specific control that will hold the value for that
field."
What does that mean?

CH!

 

[Ken Snell [MVP]]

Replace ControlName with the real name of a control that is on your form.
That control will be one of the ones you're using to do the filtering
selections.

 

[Chink!]

As far as I know I'm not doing any filtering as I want to be able to search
the entire table, so the query contains all fields.
Could you give me an example of a control that would be on the form?

thanks
CH!

 

[Ken Snell [MVP]]

People commonly use a textbox or a listbox or a combobox control on the
form.

 

[Chink!]

Ahhh! I used a text box - its Name is Text 22
so the expression should read:
Forms!AddressesQuery!Text22 Or Forms!AdressessQuery!Text22 Is Null

as the Form Name is Addresses Query
correct?
CH!

 

[Ken Snell [MVP]]

Yes.

--

Ken Snell
<MS ACCESS MVP>

Ahhh! I used a text box - its Name is Text 22
so the expression should read:
Forms!AddressesQuery!Text22 Or Forms!AdressessQuery!Text22 Is Null

as the Form Name is Addresses Query
correct?
CH!

 

[Chink!]

Thanks very much Ken


CH!

Yes.

 

[Chink!]

Ok I got stuck...

How do I put the expression in as a criteria for each field on the query?

I appreciate your patience


CH!

 

[Ken Snell [MVP]]

This is something that you will want to do the first time you create the
query. If you close the query and come back to it to continue adding more
criteria in QBE view, you'll see a mishmash of criteria that will very
difficult to work with. (You can do it more easily in SQL view, but ...)

Create the initial query by putting the correct fields on the query grid.
Then put the criterion expression, just as you posted earlier (changing the
control name for each field as needed) in the Criteria: cell under that
field. Remember: do not close the query until you've added all the criteria
expressions.

--

Ken Snell
<MS ACCESS MVP>

 

[Chink!]

Can you clairfy that for me?
I created a query using the wizard and added all the fields from the table
because I want the user to be able to search all fields
I tried putting in "Forms!AddressesQuery!Text22 Or
Forms!AdressessQuery!Text22 Is Null"
in the criteria box but I get a syntax error so obviously I'm doing somthing
wrong there
I tried putting "Forms!AddressesQuery!Text22"
in the first criteria box and that still gave me an error

Second thing ischanging the

control name for each field as needed)

In this expression Text22 is the name of the search box on the form
"Forms!AddressesQuery!Text22 Or Forms!AdressessQuery!Text22 Is Null"

Should Control Name in
Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
be the name of the each field in the query?


thanks
CH!

 

[Ken Snell [MVP]]

I'm not completely clear on what you did, but if you included the "
characters with the Forms!etc. string, then delete those " characters. You
need to put just this:
Forms!AddressesQuery!Text22 Or Forms!AdressessQuery!Text22 Is Null

ControlName in the generic example I gave you is to be the name of the
control on the form that holds the value that you want to search for in a
specific field. For each field, put the expression in the Criteria: box
under that field in the query's design view, replacing ControlName with the
correct name of the control that holds that value.

Note that, if you're using multiple controls, all of the expressions, one
for each field, go on the same "horizontal" row for the Criteria row.