searching fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with approximately 5 fields(TYPE, CASE, FNAME, LNAME,
D.O.B) what i want to be able to do is be able to do some type of a seach
where i could search each field to narrow down the search. IN addition some
records might not have a case or a type so it would just need to be searched
by LNAME and FNAME. So in the search when it is prompts that user for input
it does not necessarily have to be given input. Any suggestions
 
I just answered this for you yesterday....

To prompt a user for a parameter and allow it to be left blank for all,
you'd do something like this...


Like [Enter Customer Name] & "*"

This also allows them to enter a partial name. What it does is adds the
asterisk onto whatever they type and Access treats that as a wildcard. If
they enter "SMITH" Access will look for "SMITH*" which would find SMITH,
SMITHERS, SMITHSON, etc. If they enter nothing, Access will llok for "*"
which would find everything.

To allow searches anywhere in the string, you could put...
Like "*" & [Enter Customer Name] & "*"


You could also refer to a field on a form and concantenate the "*" to it in
case the user leaves the field blank.

Hope that helps.
Rick B
 
Note: These criteria may need to be on SEPARATE lines in the bottom.
Placing them on the same line requires they all be true (AND condition)
while separate lines create an OR condition.

Rick B
 
Yea I know you answered that for me yesterday but i tried it and it returned
an empty query after i tried to do that.

any idea on what i did wrong
 
Nope. Have you tried entering the criteria for each field alone to make
sure it works? After that, add a second criteria and see if it works. Try
putting them on the same line, then separate lines.

See if you can figure it out with the tools I gave you rather than just
posting back for a quick fix.
 
You can try criteria like the following.

Field: LastName
Criteria: Like IIF([Enter Last Name] is Null,"*",[Enter Last Name])

Field: FirstName
Criteria: Like IIF([Enter First Name] is Null,"*",[Enter First Name])

Field: DOB
Criteria: Between IIF([Enter DOB] is Null,#1/1/1899#,[Enter DOB]) AND IIF([Enter
DOB] is Null,#1/1/2099#,[Enter DOB])

Field: Type
Criteria: [Enter Type] or [Enter Type] is Null

Field: Case
Criteria: [Enter Case] or [Enter Case] is Null

Note that Access will rearrange this criteria when the query is saved, but it
will still work.
 
Back
Top