Help with criteria

[Guest]

Like [which Patient?] & "*" Or Like "*" & [which Patient?] & "*"


how can i make this show all records if the parameter criteria left blank.

 

[Guest]

Thank you Mr ofer .
actually I have already tried this but getting wrong results.
I am trying to creat a query with multi criteria : patient name , hospital
etc.
for the patient name it is working ok , because each record must have a
patient name (the field "Patient" in each record is not blank)
but for the field Hospital , some records have this field null (blank) that
is why the result coming is wrong , I have 1865 records , out of them there
are only 792 records with the Hospital name entered .so , when i use this
expression I get only 792 although the total records are 1865.
how can I solve this.

 

[John Spencer]

One way. Create a calculated field for the hospital field that forces nulls
to strings and search against that

Field: Hospital: [TableName].[Hospital] & ""
Criteria: Like "*" & [Which Hospital] & "*"

With less than 10,000 records the search will probably be sufficiently fast
for you.

An alternative is to use the following
Field: Hospital
Criteria: Like "*" & [Which Hospital] & "*" Or [Which Hospital] is Null

Access will reformat the criteria when you close and save the query, but
unless you have a large number of fields that use this type of critieria
against the query will run.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

thanks very much . that works perfect .