Search function

[Fiona]

Is it possible to create a search button to search the whole of my access
database without removing the filter? I would like it to search for all
matches, even if the entire word isn't the same, i.e. if I type in 'tech' it
finds all matches to technology, technician etc..
Thanks
Fiona

 

[Jeff Boyce]

Fiona

I may be mis-interpreting your question...

It sounds like you want the search function to check across all fields. In
a well-normalized relational database, you'd only need to search in one
field.

To do THAT, you can use a query (or a filter) that incorporates the "Like"
operator, and wildcards. In your example, you could look for:
Like tech*

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Dave180]

"I may be mis-interpreting your question..."
I expect so too ... ... or not.

I have a "very-well-normalised" database.
I've built a bespoke Search Form for my own Db
Option-group Select-Case args string several fields together and are applied
to a filter on the form for "names" "addresses" etc.
But I still want a "General Search" Option (allbeit slow) - (that would
include all fields (including MEMO types)) ... and you can't apply filters to
a form without specifying a field on which to filter ... or can you?

I would like to know (and I suspect that Fiona and many others would
appreciate)
how to replicate the built-in MSA search dialog <sic> box

I may even use it (the built-in one) - if I were able to change the Title
etc.

So - despite being normalised - I still want to search across all fields
FindFirst; previous and next on a form. How's that done?

 

[Jeff Boyce]

From a technical standpoint, I suppose one option would be to write a
procedure that cycles through every field and conducts the search inside
each loop through.

From a practical standpoint, perhaps you and I have a different definition
of "normalized". I'm having trouble imagining a well-normalized database
structure that could have a value show up in any field in a table. The
whole purpose of normalizing is to ensure that the data you need is in ONE
field (or if you need different data, you look in another field).

JOPO (just one person's opinion)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Dave180]

You are certainly entitled to your opinion.
My database however contains a MEMOtype field for free-form notes.
So on top of all the NORMALISED data regarding my customers; their PCs their
Printers; My Invoices; their "relationships/referals"; notes on OS work and
Mailing extract data - I'd like to put a few comments in too!
Shouldn't be too much of a hardship to expect to search a sub-form of notes!
Dave.

 

[Jeff Boyce]

Dave

It sounds like you are supporting the use of a single MEMO field to store
the notes. I was trying to state that a single field to hold notes makes
more sense than scattering them all over the fields.

I've used both a MEMO field with date/time inserts to help separate the
notes, and have uses a Notes/Comments table with NoteDateTime, Noter, Note
fields (Note as a 255 Text field). This latter approach makes it a bit
easier to find Noter and DateTime info, instead of having to parse it out of
a MEMO. I suppose adding a TypeOf field would also allow for notes on
whatever topic.

Regards

Jeff Boyce
Microsoft Office/Access MVP