Help on a QBF multi criteria Search

[Timothy]

Does anyone have a code sample or example database that
uses a QBF. The form has 2 or 3 search boxes that narrow
the search. if one or two of the criteria is left blank
then no results are returned. ?????

Ive gotten loads of help so far and ive come to this point:
I have 3 tables (joined by a one to one) that Im running
this query using is not null and Like "*" & ...& "*","XXX"
on every field of the tables. I have two criteria boxes,
but it only gives the results from each box, as if its
seperate. (searching for Bob and Smith. I would get all
the records with bob and all the records with smith,
instead of the records only with bob smith.) I was told it
was too dificult to do in the query and needed a SQL
code. But,
Is there a statement, conditional, that says Compare the
results and only display the ones whose records are
Identical? maybe their primary key (record number)???
what would the statement look like?

Or will... run the 3 querys (one for each table..maybe I
can combine them into one query since they have a one to
one.) off the first entry box, make 3 more identical
queries and connect them to the second criteria box, and
the same for the 3rd. Then write a query that compares
the results of the queries and only displays the identical
records. Even if box 2 or 3 are left blank (null) then
it would just do it for the info it has.
Is this even possible. No one has given me anything close
that will narrow a search.
any other ideas would be greatly appreciated.


Timothy

 

[Steve Schapel]

Timothy,

I have not quite grasped exactly what you are trying to do, and I do
not understand the criteria ideas you mentioned. But let's put it
this way... If you have a form called MySearch and two unbound
textboxes named 1stCrit and 2ndCrit, and in the criteria of the
FirstName field in your query you put...
[Forms]![MySearch]![1stCrit] Or [Forms]![MySearch]![1stCrit] Is Null
.... and in the criteria of the LastName field in your query you put...
[Forms]![MySearch]![2ndCrit] Or [Forms]![MySearch]![2ndCrit] Is Null
.... then, if you put bob in the 1stCrit textbox and leave the other
one blank, your query will return all records where the FirstName is
Bob, and if you put bob in the 1stCrit textbox and smith in the
2ndCrit textbox, your query will return only Bob Smith.

Another approach to entering the criteria is to use...
Like Nz([Forms]![MySearch]![1stCrit],"*")

- Steve Schapel, Microsoft Access MVP

 

[Timothy]

The reason for this query is this:
Its police related that holds suspect information.
name, race, dob, ht, wt, hair , eyes, alias, know assoc,
comments, vehicles....ect

I need a qbf that will narrow a search if
Im get a call about a guy named bill who has a tattoo of a
skull is robbing people with a corkscrew.
my form now will give 3000 hits on bill, 47 unrelated hits
on skull , and 14 unrelated hits on corkscrew.
then i have to manually narrow it down by seeing whos got
all three criteria in thier records.


I want to be able to type bill then skull and return 12
hits or all three words and get the 1 hit that is the
suspect.
But if a box is left blank the query will return a null
for that search.

Basically the more criteria i enter the fewer records i
get.hope that clears it up.

Timothy

 

[John Vinson]

The reason for this query is this:
Its police related that holds suspect information.
name, race, dob, ht, wt, hair , eyes, alias, know assoc,
comments, vehicles....ect

What you should consider is building the SQL string of the query in
code. You can have multiple textboxes on the form, one for each field
being searched; in code on a command button on the form, you would
poll through all the textboxes. If a textbox is null you wouldn't do
anything, otherwise you would add

AND [fieldname] = {form control value}

to a growing SQL string. When the string is complete you can use it as
the Recordsource or Filter for a form.

 

[Steve Schapel]

Timothy,

Thanks for the further information. However, if you read my earlier
response, I think you will find I have already answered your request.

- Steve Schapel, Microsoft Access MVP

 

[Timothy]

Sorry Steve, didnt work. Now it doesnt return ANY
records, ive tried with all the syntax. By putting the
statement where you say it goes say its looking for a
FiRST nam AND Last name only, unless i leave that crite
blank, then it returns the first name.

but the way this is set up I would be look for ANY
matching criteria in ANY field of the 3 tables. So the
statements are listed 1 row under the previous.
IE firstcrit BOB secondcrit Smith. would bring back
bob smith, charles fuller who lives on SMITH st and is a
Know assoc. of BOB jones....
or if the next search is anyone who drives a yellow car
and lives on smith st.

Ive got it to work where the fist box returns results and
the second returns results but its showing ALL the
results, not only the records that match both searches.

Thannks for your help....I knew this was a toughie when
the bosses asked for it.

Timothy

 

[Steve Schapel]

Timothy,

Yes, it's a lot more complicated if you want the criteria entered in
each box to apply to more than one field in the query.

- Steve Schapel, Microsoft Access MVP

 

[Timothy]

I know....so How do I do it? Any Ideas or sample
Databases...THis cant be such a unusuah request, many of
the old programs, probably on dbase or even older, that we
use for different things do this, but I dont have access
to the code.

Timothy

-----Original Message-----
Timothy,

Yes, it's a lot more complicated if you want the criteria entered in
each box to apply to more than one field in the query.

- Steve Schapel, Microsoft Access MVP

 

[timothy]

I did say in my very first question that i have a query
using LIKE "*" &... in every field of every table that
would search for the imputed criteria.
I know I could make a form with every field of every table
and have people just enter info to the appropriate field,
but there would be over 40-50 fields so that would not be
practical.
DO you have the code or statement to do this? if so what
is it.

Thank you

Timothy

 

[Steve Schapel]

Timothy,

When you say 40-50 fields, does this mean you don't really know how
many? And how about the other part of my question? You are not
providing sufficient information as to what you want.

- Steve Schapel, Microsoft Access MVP

 

[John Spencer (MVP)]

I did have an idea on how you MIGHT be able to do this. You would need to
concatenate ALL the fields together into a calculated field and then search
against that. That might look something like the following. This would work
with string fields, but you might run into limits on the number of characters
allowed in a cell. This may be pretty slow, but you can try it.

SELECT TableA.*
FROM TableA
WHERE
TableA.FieldA & "/" & TableA.FieldB & "/" & TableA.FieldC & ....
LIKE "*" & [SearchStringOne] & "*"
AND
TableA.FieldA & "/" & TableA.FieldB & "/" & TableA.FieldC & ....
LIKE "*" & [SearchStringTwo] & "*"
AND
TableA.FieldA & "/" & TableA.FieldB & "/" & TableA.FieldC & ....
LIKE "*" & [SearchStringThree] & "*"

If that doesn't work, what version of Access are you using? Can you email the
structure and perhaps a few sample (sanitized) records? I _MAY_ be able to take
a look at it and come up with the code to build your SQL statements. DON'T send
the information yet. Try the above and post back on your results.

Also, are your three tables related or are you searching them separately?

I thought I made it very clear with the explaination, and
examples, and further explaination. But I guess I wasn't
getting my idea across....I have 23 fields in one table,
15 fields in a second table, and 18 fields in a third
table.
Yes, I need to search every field for the matching
criteria, no matter what field of which table it is found,
then it would display ony the records where ALL the
criteria are matched in any of the fields.
Hope this helps
Timothy

 

[Timothy]

Just answering the question youve been a great help.
Im am a newbie when it comes to sql, so slowly catching on
to it.
I have tried something that seems to work, but its the
long way. Maybe you can tell me how to clean it up or
make it work.

I now have 2 queries running off each criteria box. Qry1
and qry2 which search each table. IE: Searchqry runs off
Entry and searches Main Table for the criteria. Searchqry1
runs off the 2nd box (Entry1) which searchs the main table
for the other criteria. There are 4 more qrys that do the
same for the other 2 tables.
Now I have yet another query that compares searchqry and
searchqry1 joined at the record number (primary key
autonumber)By dragging the * to the qry line. so the query
runs and only shows the record where both criteria are
found.
That works, except if I leave the second criteria blank,
then I get no records at all. Is there a condition I can
enter into the QBE or maybe a macro that says: If the 2nd
or 3rd(trying to add another) box are null then show the
results of the first qry without comparing????
I dont mind sending you a copy of the table and qrys. just
let me know.
My email is (e-mail address removed) send a email and we
can discuss it.

Thank you
TImothy