Fill in some or all criteria

[Sheila D]

I have a report where the underlying query gets its croteria from a form.
There are 3 selection fields and ifthey are all completed it works fie. I
want to be able to leave 1 or more blank and for those the report returns all
records that match other critria that is selected. My SQL currently looks
like this:
SELECT T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy") AS
[Year], Format([Date_Of_Procedure],"mm") AS GrpMth,
Format([Date_Of_Procedure],"mmmm") AS [Month], T_Patients.Surname,
T_Patients.Forename, [Forename] & " " & [Surname] AS Name,
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure
FROM (T_Patients INNER JOIN T_Procedures ON T_Patients.Hospital_Number =
T_Procedures.Hospital_Number) INNER JOIN T_Procedure_Type ON
T_Procedures.Procedure_Number = T_Procedure_Type.Procedure_Number
WHERE (((T_Procedures.Consultant) Like
IIf([Forms]![ChooseF_Consultant_History]![Consultant] Is
Null,"*",[Forms]![ChooseF_Consultant_History]![Consultant])) AND
((T_Procedures.Date_Of_Procedure) Between
[Forms]![ChooseF_Consultant_History]![Start Date] And
[Forms]![ChooseF_Consultant_History]![End Date])) OR
((([Forms]![ChooseF_Consultant_History]![Start Date]) Is Null)) OR
((([Forms]![ChooseF_Consultant_History]![End Date]) Is Null))
GROUP BY T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy"),
Format([Date_Of_Procedure],"mm"), Format([Date_Of_Procedure],"mmmm"),
T_Patients.Surname, T_Patients.Forename, [Forename] & " " & [Surname],
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure;

The Consultant part on it's own is fine but I can't get the dates to work in
conjunction with this - any help much appreciated

Sheila

 

[Allen Browne]

You already have a form where the user enters the limiting criteria, so add
a command button to the form to fire off the report.

The command button's Click event procedure can build a filter string from
just the boxes where the user actually entered something, and then use that
as the WhereCondition for OpenReport. This way, you leave the criteria out
of the query completely. The end result is a much more efficient filter that
contains only the phrases you really need.

If you have never built a filter string like that before, it will take a bit
of effort to wrap your head around it, but it's worth the effort. You do
exactly the same thing to create a search form (applying a filter to a form)
as you do to limit the report (applying a filter to a report.)

Download the example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Pull it apart, and see how it works.

If you really don't want to go that way, the end of the article does explain
the convoluted logic you can use to do the whole task in the query. Scroll
down to 'Using a query instead.'

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report where the underlying query gets its croteria from a form.
There are 3 selection fields and ifthey are all completed it works fie. I
want to be able to leave 1 or more blank and for those the report returns
all
records that match other critria that is selected. My SQL currently looks
like this:
SELECT T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy") AS
[Year], Format([Date_Of_Procedure],"mm") AS GrpMth,
Format([Date_Of_Procedure],"mmmm") AS [Month], T_Patients.Surname,
T_Patients.Forename, [Forename] & " " & [Surname] AS Name,
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure
FROM (T_Patients INNER JOIN T_Procedures ON T_Patients.Hospital_Number =
T_Procedures.Hospital_Number) INNER JOIN T_Procedure_Type ON
T_Procedures.Procedure_Number = T_Procedure_Type.Procedure_Number
WHERE (((T_Procedures.Consultant) Like
IIf([Forms]![ChooseF_Consultant_History]![Consultant] Is
Null,"*",[Forms]![ChooseF_Consultant_History]![Consultant])) AND
((T_Procedures.Date_Of_Procedure) Between
[Forms]![ChooseF_Consultant_History]![Start Date] And
[Forms]![ChooseF_Consultant_History]![End Date])) OR
((([Forms]![ChooseF_Consultant_History]![Start Date]) Is Null)) OR
((([Forms]![ChooseF_Consultant_History]![End Date]) Is Null))
GROUP BY T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy"),
Format([Date_Of_Procedure],"mm"), Format([Date_Of_Procedure],"mmmm"),
T_Patients.Surname, T_Patients.Forename, [Forename] & " " & [Surname],
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure;

The Consultant part on it's own is fine but I can't get the dates to work
in
conjunction with this - any help much appreciated

Sheila

 

[Klatuu]

Remove all the filtering from the query and use the Where argument of the
OpenReport method to do your filtering.
The Where argument looks just like an SQL WHERE Clause without the word where.
Barring any syntax in this "air code", this is what you need:

Dim strWhere As String

If Not IsNull(Me.Consultant) Then
strWhere = "[T_Procedures].[Consultant] = """ & Me.Consultant
End If

If Not IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then
'Start and End Dated filled in
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " & _
"BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
Else
'Start Filled, End Null
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " & _
">= #" & Me.StartDate & "#"
End If
Else
'Start Null, End Filled
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " & _
"<= #" & Me.EndDate & "#"
End If
Docmd.OpenReport "MyReport, , , strWhere


************
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities"
GoTo AddAnd_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

I have a report where the underlying query gets its croteria from a form.
There are 3 selection fields and ifthey are all completed it works fie. I
want to be able to leave 1 or more blank and for those the report returns all
records that match other critria that is selected. My SQL currently looks
like this:
SELECT T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy") AS
[Year], Format([Date_Of_Procedure],"mm") AS GrpMth,
Format([Date_Of_Procedure],"mmmm") AS [Month], T_Patients.Surname,
T_Patients.Forename, [Forename] & " " & [Surname] AS Name,
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure
FROM (T_Patients INNER JOIN T_Procedures ON T_Patients.Hospital_Number =
T_Procedures.Hospital_Number) INNER JOIN T_Procedure_Type ON
T_Procedures.Procedure_Number = T_Procedure_Type.Procedure_Number
WHERE (((T_Procedures.Consultant) Like
IIf([Forms]![ChooseF_Consultant_History]![Consultant] Is
Null,"*",[Forms]![ChooseF_Consultant_History]![Consultant])) AND
((T_Procedures.Date_Of_Procedure) Between
[Forms]![ChooseF_Consultant_History]![Start Date] And
[Forms]![ChooseF_Consultant_History]![End Date])) OR
((([Forms]![ChooseF_Consultant_History]![Start Date]) Is Null)) OR
((([Forms]![ChooseF_Consultant_History]![End Date]) Is Null))
GROUP BY T_Procedures.Consultant, Format([Date_Of_Procedure],"yyyy"),
Format([Date_Of_Procedure],"mm"), Format([Date_Of_Procedure],"mmmm"),
T_Patients.Surname, T_Patients.Forename, [Forename] & " " & [Surname],
T_Procedure_Type.Procedure_Number, T_Procedure_Type.Procedure_Type,
T_Procedures.Hospital_Number, T_Procedures.Date_Of_Procedure;

The Consultant part on it's own is fine but I can't get the dates to work in
conjunction with this - any help much appreciated

Sheila

 

[John Spencer]

If you feel you have to do this in the query, then you can modify the where
clause to the following.

WHERE T_Procedures.Consultant Like
NZ([Forms]![ChooseF_Consultant_History]![Consultant],"*")

AND (T_Procedures.Date_Of_Procedure >=
[Forms]![ChooseF_Consultant_History]![Start Date] OR
[Forms]![ChooseF_Consultant_History]![Start Date] is Null)

AND T_Procedures.Date_Of_Procedure <=
[Forms]![ChooseF_Consultant_History]![End Date] OR
[Forms]![ChooseF_Consultant_History]![End Date] Is Null)


This assumes that Consultant always has a value or that you don't want to
return records where consultant is null.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

I prefer

AND (T_Procedures.Date_Of_Procedure >=
Nz([Forms]![ChooseF_Consultant_History]![Start Date], #1/1/100#)

AND T_Procedures.Date_Of_Procedure <=
Nz([Forms]![ChooseF_Consultant_History]![End Date], #12/31/9999#)

although I will concede that it will return different results when no dates
are provided on the form and Date_Of_Procedure is ever Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If you feel you have to do this in the query, then you can modify the
where clause to the following.

WHERE T_Procedures.Consultant Like
NZ([Forms]![ChooseF_Consultant_History]![Consultant],"*")

AND (T_Procedures.Date_Of_Procedure >=
[Forms]![ChooseF_Consultant_History]![Start Date] OR
[Forms]![ChooseF_Consultant_History]![Start Date] is Null)

AND T_Procedures.Date_Of_Procedure <=
[Forms]![ChooseF_Consultant_History]![End Date] OR
[Forms]![ChooseF_Consultant_History]![End Date] Is Null)


This assumes that Consultant always has a value or that you don't want to
return records where consultant is null.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Remove all the filtering from the query and use the Where argument of the
OpenReport method to do your filtering.
The Where argument looks just like an SQL WHERE Clause without the word
where.
Barring any syntax in this "air code", this is what you need:

Dim strWhere As String

If Not IsNull(Me.Consultant) Then
strWhere = "[T_Procedures].[Consultant] = """ & Me.Consultant
End If

If Not IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then
'Start and End Dated filled in
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " &
_
"BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
Else
'Start Filled, End Null
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " &
_
">= #" & Me.StartDate & "#"
End If
Else
'Start Null, End Filled
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[T_Procedures].[Date_Of_Procedure] " &
_
"<= #" & Me.EndDate & "#"
End If
Docmd.OpenReport "MyReport, , , strWhere

************ Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities"
GoTo AddAnd_Exit

End Function