Multiple filter query of form

T

truepantera

I have a form which receives the input for a multiple entry search. The form
then opens a report to show the restricted data. I cannot get it to work
properly!

I have added the following coding to each of my search fields on my query.

Technician Name field:
Forms![Search Selection Dialog]![findtech] or Forms![Search Selection
Dialog]![findtech] is Null

I have seven of these seach criteria on the search form and it will not
restrict the records. Access made additional entries for me (creating
additional colums in the query). It worked when I only used two fields for
filtering!!! There has to be a better way!
 
N

Nikos Yannacopoulos

Try something along the lines of:

(Like "*" & Forms![Search Selection Dialog]![findtech] & "*") Or Is Null


HTH,
Nikos
 
T

truepantera

I just tried it and with that line I get request for input on the fields I
don't type info in. Those fields I want to include all records and currently
when I just enter through those pop-up fields, it does not restrict my
records.

P
 
N

Nikos Yannacopoulos

truepantera said:
Those fields I want to include all records
Click to expand...
This is what it should be doing, if you're getting "pop-up fields"
(input boxes) it's mosty likely because you have misspelled something in
the form control reference, or forgotten parameter declarations (look in
Query > Parameters).

Nikos
 
T

truepantera

My SQL

SELECT [Internal Chart Audit].Dateofentry, [Internal Chart
Audit].[Technician Name], [Internal Chart Audit].[Scribe Name], [Internal
Chart Audit].[Patient Name], [Internal Chart Audit].[Month audited],
[Internal Chart Audit].[Examination date]
FROM [Internal Chart Audit]
WHERE ([Internal Chart Audit].Dateofentry <= [Forms]![Search Selection
Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) >=
[Forms]![Search Selection Dialog]![beginningdate] or [Internal Chart
Audit].Dateofentry <= [Forms]![Search Selection Dialog]![EndingDate] And
([Internal Chart Audit].Dateofentry) >= [Forms]![Search Selection
Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] =
[Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection
Dialog]![findtech] Is Null OR [Internal Chart Audit].Dateofentry <=
[Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart
Audit].Dateofentry) >= [Forms]![Search Selection Dialog]![beginningdate] AND
[Internal Chart Audit].[Technician Name] = [Forms]![Search Selection
Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null
AND [Internal Chart Audit].[Scribe Name] = [Forms]![Search Selection
Dialog]![findscribe] Or [Forms]![Search Selection Dialog]![findscribe] Is
Null OR [Internal Chart Audit].Dateofentry <= [Forms]![Search Selection
Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) >=
[Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart
Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or
[Forms]![Search Selection Dialog]![findtech] Is Null AND [Internal Chart
Audit].[Scribe Name] = [Forms]![Search Selection Dialog]![findscribe] Or
[Forms]![Search Selection Dialog]![findscribe] Is Null AND [Internal Chart
Audit].[Patient Name] =[Forms]![Search Selection Dialog]![findpatname] Or
[Forms]![Search Selection Dialog]![findpatname] Is Null OR [Internal Chart
Audit].Dateofentry <= [Forms]![Search Selection Dialog]![EndingDate] And
([Internal Chart Audit].Dateofentry) >= [Forms]![Search Selection
Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] =
[Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection
Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] =
[Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection
Dialog]![findscribe] Is Null AND [Internal Chart Audit].[Patient Name]
=[Forms]![Search Selection Dialog]![findpatname] Or [Forms]![Search Selection
Dialog]![findpatname] Is Null AND [Internal Chart Audit].[Month audited]
=[Forms]![Search Selection Dialog]![findauditmonth] Or [Forms]![Search
Selection Dialog]![findauditmonth] Is Null OR [Internal Chart
Audit].Dateofentry <= [Forms]![Search Selection Dialog]![EndingDate] And
([Internal Chart Audit].Dateofentry) >= [Forms]![Search Selection
Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] =
[Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection
Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] =
[Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection
Dialog]![findscribe] Is Null AND [Internal Chart Audit].[Patient Name]
=[Forms]![Search Selection Dialog]![findpatname] Or [Forms]![Search Selection
Dialog]![findpatname] Is Null AND [Internal Chart Audit].[Month audited]
=[Forms]![Search Selection Dialog]![findauditmonth] Or [Forms]![Search
Selection Dialog]![findauditmonth] Is Null AND [Internal Chart Audit].[Workup
Qty] =[Forms]![Search Selection Dialog]![findexamdate] Or [Forms]![Search
Selection Dialog]![findexamdate] is Null)
 
N

Nikos Yannacopoulos

I am truly amazed how you produced this. This is not what I suggested
two posts ago. Your criteria are a mess, they are repeating all over the
place, you are allowing for Null in the wrong place (form controls
instead of table fields) etc. Go back to my first reply, see the
suggested syntax for criteria and try again. Suggest you srat fresh,
rather than editing your existing query. If all goes well, you should
get something like:

SELECT [Internal Chart Audit].Dateofentry, [Internal Chart
Audit].[Technician Name], [Internal Chart Audit].[Scribe Name],
[Internal Chart Audit].[Patient Name], [Internal Chart Audit].[Month
audited], [Internal Chart Audit].[Examination date]
FROM [Internal Chart Audit]
WHERE [Internal Chart Audit].Dateofentry <= [Forms]![Search Selection
Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) >=
[Forms]![Search Selection Dialog]![beginningdate]
AND [Internal Chart Audit].[Technician Name] Like "*" & [Forms]![Search
Selection Dialog]![findtech] & "*" Or Is Null
AND [Internal Chart Audit].[Scribe Name] Like "*" & [Forms]![Search
Selection Dialog]![findscribe] & "*" Or Is Null
AND [Internal Chart Audit].[Patient Name] Like "*" & [Forms]![Search
Selection Dialog]![findpatname] & "*" Or Is Null
AND [Internal Chart Audit].[Month audited] Like "*" & [Forms]![Search
Selection Dialog]![findauditmonth] & "*" Or Is Null
AND [Internal Chart Audit].[Workup Qty] Like "*" & [Forms]![Search
Selection Dialog]![findexamdate] & "*" Or Is Null

Note:
The trick with the wildcards doesn't work well with numbers and dates.
For the dates, I suggest you use a very wide range as default in the
form (like 01/01/1900 to 12/31/2999) so you don't mess with nulls (which
would requre code to handle correctly), while all your records are
inclided; the user will only need change those if they really want a
specific range.
Likewise on the qty field; Null will work fine with this trick (all
records returned), when looking for a particular value you can filter
but if you enter, say, 12.85 you will also get records with 612.85,
12.854 etc (search istreated like a string). Again, for true filtering
and allowing for Null => all records, you would need code.

Nikos
 
N

Nikos Yannacopoulos

Oops! Forgot the parentheses to force condition prioroties:

SELECT [Internal Chart Audit].Dateofentry, [Internal Chart
Audit].[Technician Name], [Internal Chart Audit].[Scribe Name],
[Internal Chart Audit].[Patient Name], [Internal Chart Audit].[Month
audited], [Internal Chart Audit].[Examination date]
FROM [Internal Chart Audit]
WHERE ([Internal Chart Audit].Dateofentry <= [Forms]![Search Selection
Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) >=
[Forms]![Search Selection Dialog]![beginningdate])
AND ([Internal Chart Audit].[Technician Name] Like "*" & [Forms]![Search
Selection Dialog]![findtech] & "*" Or Is Null)
AND ([Internal Chart Audit].[Scribe Name] Like "*" & [Forms]![Search
Selection Dialog]![findscribe] & "*" Or Is Null)
AND ([Internal Chart Audit].[Patient Name] Like "*" & [Forms]![Search
Selection Dialog]![findpatname] & "*" Or Is Null)
AND ([Internal Chart Audit].[Month audited] Like "*" & [Forms]![Search
Selection Dialog]![findauditmonth] & "*" Or Is Null)
AND ([Internal Chart Audit].[Workup Qty] Like "*" & [Forms]![Search
Selection Dialog]![findexamdate] & "*" Or Is Null)
 
Top