Prev: Cascade 13 Combo Boxes...
Next: using VBA to change property of a field
From: dgodfrey on 2 Jul 2008 17:02
Here is a silly question.

Is there an easy way to enter a single search term in a text box and have
Access search every field (control, etc.) for matching results
"automagically" as the saying goes? Some sort of wildcard, maybe?

For example, a user enters a Driver's License # or a last name. Access then
returns a list of all records that have that particular name or number or
whatever. Kind of a blind search, because they have insufficient information
to search any particular field by name or whatever. They have been given one
piece of information...say for example an even more broad search...they want
to find all white females in the database. Instead of having to have these
two fields represented on the search form by combo boxes, just enter "White
Female" and it comes back with 10 records that pertain to a white female.

I hope this makes sense and can it be done? Should it be done?

Thanks.
From: KARL DEWEY on 2 Jul 2008 19:19
I did not complete the process but this will get you started.
Create a union query pulling the primary key field and each and every field
you want to search. Then use it in a second query.
What I did not finish was to parse the input information entered in the
prompt [Enter words].

QRY Full_Search1 --
SELECT YourTable.ID, YourTable.PA AS SOME
FROM YourTable
UNION ALL SELECT YourTable.ID, YourTable.PDX AS SOME
FROM YourTable
UNION ALL SELECT YourTable.ID, YourTable.Tot_PA AS SOME
FROM YourTable
UNION ALL SELECT YourTable.ID, YourTable.Power AS SOME
FROM YourTable
UNION ALL SELECT YourTable.ID, YourTable.Product AS SOME
FROM YourTable;

QRY Full_Search2 --
SELECT YourTable.ID, YourTable.PA, YourTable.PDX, YourTable.Tot_PA,
YourTable.YourDate
FROM YourTable, Full_Search1
WHERE (((Full_Search1.ID)=[YourTable].[ID]) AND ((Full_Search1.[SOME]) Like
"*" & [Enter words] & "*"))
GROUP BY YourTable.ID, YourTable.PA, YourTable.PDX, YourTable.Tot_PA,
YourTable.YourDate;

--
KARL DEWEY
Build a little - Test a little


"dgodfrey" wrote:

> Here is a silly question.
>
> Is there an easy way to enter a single search term in a text box and have
> Access search every field (control, etc.) for matching results
> "automagically" as the saying goes? Some sort of wildcard, maybe?
>
> For example, a user enters a Driver's License # or a last name. Access then
> returns a list of all records that have that particular name or number or
> whatever. Kind of a blind search, because they have insufficient information
> to search any particular field by name or whatever. They have been given one
> piece of information...say for example an even more broad search...they want
> to find all white females in the database. Instead of having to have these
> two fields represented on the search form by combo boxes, just enter "White
> Female" and it comes back with 10 records that pertain to a white female.
>
> I hope this makes sense and can it be done? Should it be done?
>
> Thanks.
 | 
Pages: 1
Prev: Cascade 13 Combo Boxes...
Next: using VBA to change property of a field