Approach

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

Guest

I have a variety of reports that need to be filtered I am not sure of the
approach to take. Should I create a different query for each type of filter.
Then use those queries to develop the query for the report? How should I do
this to make life easy for the programmer (me), the users, the database, and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
 
I generally create reports based on queries that have little or no criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.
 
If its 1 report where ALL of the fields and tables remain the SAME, but
the CRITERIA changes. You have a couple of options.

1. You can use a variation of Query By Form to build the criteria and
then apply it using the whereStatement parameter of DoCmd.OpenReport

2. You can create a table that contains the whereStatements already
created by you and a short description. The scheme would look like...

txtShortDesc(key) txtLongDesc strWhereStatement
BANKOUT Cashier Bankout (all) [txtCasherID] = *
OVERSHORT Over/Short Report [variance] <> 0

Then create a form with a comboBox or listBox that displays the values
in the table above with a 'PRINT REPORT' command button that opens the
report using the appropriate whereStatement. Obtained by either using a
DLookup() or the comboBox/listBox column property.

David H
 
Thanks Duane for your answer. It seems like I will be in for some work. I am
try to get the date selection to work. This is what I have and it breaks at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

Duane Hookom said:
I generally create reports based on queries that have little or no criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

Fay said:
I have a variety of reports that need to be filtered I am not sure of the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should I
do
this to make life easy for the programmer (me), the users, the database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
 
I'm thinking that you could possibly condense all of that to

Sub Report_OnOpen()

strSQL = "SELECT .... "

If [Forms]![myForm]![txtCriteria] <> "" then
strSQL = strSQL & "WHERE [Forms]![myForm]![DateOfClassStart] " &
Me.txtCriteria & "#" & [Forms]![myForm]![txtRosterStart] & "#"
end if

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub

This will dynamically create & change the recordSource for the report
based on the various condition when the report is opened from the form.
Thanks Duane for your answer. It seems like I will be in for some work. I am
try to get the date selection to work. This is what I have and it breaks at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:

I generally create reports based on queries that have little or no criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

I have a variety of reports that need to be filtered I am not sure of the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should I
do
this to make life easy for the programmer (me), the users, the database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
If you are modifying the SQL property of a saved query, then you shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition, the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after the
"WHERE ".

--
Duane Hookom
MS Access MVP


Fay said:
Thanks Duane for your answer. It seems like I will be in for some work. I
am
try to get the date selection to work. This is what I have and it breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

Duane Hookom said:
I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

Fay said:
I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
Questions about what you sent and thank you for sending it. I know nothing
about SQL and my needs have me in over my head.

Private Sub Report_Open(Cancel As Integer)
" Doesn't the strSql have to be Dim
Dim strSql As String
strSql = "SELECT .... "

If [Forms]![frmReports]![txtCriteria] <> "" Then
'Doesn't the Where line have to be directed towards the Report?
strSql = strSql & "WHERE [Reports]![rptQuery7]![DateOfClassStart] " & _
[Forms]![frmReports]![txtCriteria] & "#" &
[Forms]![frmReports]![txtRosterStart] & "#"
End If

'What is this doing when you add the ";"?
strSql = strSql & ";"

Me.RecordSource = strSql

End Sub

This is good for one criteria. If I had a second criteria does it become
strSql1?

David C. Holley said:
I'm thinking that you could possibly condense all of that to

Sub Report_OnOpen()

strSQL = "SELECT .... "

If [Forms]![myForm]![txtCriteria] <> "" then
strSQL = strSQL & "WHERE [Forms]![myForm]![DateOfClassStart] " &
Me.txtCriteria & "#" & [Forms]![myForm]![txtRosterStart] & "#"
end if

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub

This will dynamically create & change the recordSource for the report
based on the various condition when the report is opened from the form.
Thanks Duane for your answer. It seems like I will be in for some work. I am
try to get the date selection to work. This is what I have and it breaks at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:

I generally create reports based on queries that have little or no criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--


I have a variety of reports that need to be filtered I am not sure of the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should I
do
this to make life easy for the programmer (me), the users, the database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
Okay I took the ,,strSql out of the DoCmd line.

What makes no sense is your comment about the Where Clause.

Thanks Fay

Duane Hookom said:
If you are modifying the SQL property of a saved query, then you shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition, the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after the
"WHERE ".

--
Duane Hookom
MS Access MVP


Fay said:
Thanks Duane for your answer. It seems like I will be in for some work. I
am
try to get the date selection to work. This is what I have and it breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

Duane Hookom said:
I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
I should have also said that I got the report to open this time but the
criterial did not limit the results displayed. I got the whole list of dates.

Current code below:

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview

End Sub

Duane Hookom said:
If you are modifying the SQL property of a saved query, then you shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition, the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after the
"WHERE ".

--
Duane Hookom
MS Access MVP


Fay said:
Thanks Duane for your answer. It seems like I will be in for some work. I
am
try to get the date selection to work. This is what I have and it breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

Duane Hookom said:
I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
hmmmm, did you open "Query7" to view the SQL?
Did you set any breakpoints to step through your code to see what was going
on?
Is Query7 the record source of your report?

--
Duane Hookom
MS Access MVP


Fay said:
I should have also said that I got the report to open this time but the
criterial did not limit the results displayed. I got the whole list of
dates.

Current code below:

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview

End Sub

Duane Hookom said:
If you are modifying the SQL property of a saved query, then you
shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition,
the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after
the
"WHERE ".

--
Duane Hookom
MS Access MVP


Fay said:
Thanks Duane for your answer. It seems like I will be in for some work.
I
am
try to get the date selection to work. This is what I have and it
breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:

I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--

I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type
of
filter.
Then use those queries to develop the query for the report? How
should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for
each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
Fay said:
Questions about what you sent and thank you for sending it. I know nothing
about SQL and my needs have me in over my head.

Private Sub Report_Open(Cancel As Integer)
" Doesn't the strSql have to be Dim
Click to expand...
Yes it should be. I wrote the code off the top of my head.
Dim strSql As String
strSql = "SELECT .... "

If [Forms]![frmReports]![txtCriteria] <> "" Then
'Doesn't the Where line have to be directed towards the Report?
Click to expand...
Since the SQL statement that you're building will become the reports
recordSource (below) it is not neccessary to provide the full reference
to the field. It is however neccessary to provide the full ref to the
field on the form since it resides on a foreign object.
strSql = strSql & "WHERE [Reports]![rptQuery7]![DateOfClassStart] " & _
[Forms]![frmReports]![txtCriteria] & "#" &
[Forms]![frmReports]![txtRosterStart] & "#"
End If

'What is this doing when you add the ";"?
strSql = strSql & ";"
Click to expand...
This merely indicates the end of the SQL statement. It is placed here
because it needs to come at the end of the statement.
Me.RecordSource = strSql

End Sub

This is good for one criteria. If I had a second criteria does it become
strSql1?
Click to expand...
NO. Depending on how and what the second criteria is you would modify
the code accordingly. To elborate further, I would need to know more
about the situation(s) that would result in a second criteria.
:

I'm thinking that you could possibly condense all of that to

Sub Report_OnOpen()

strSQL = "SELECT .... "

If [Forms]![myForm]![txtCriteria] <> "" then
strSQL = strSQL & "WHERE [Forms]![myForm]![DateOfClassStart] " &
Me.txtCriteria & "#" & [Forms]![myForm]![txtRosterStart] & "#"
end if

strSQL = strSQL & ";"

Me.RecordSource = strSQL

End Sub

This will dynamically create & change the recordSource for the report
based on the various condition when the report is opened from the form.
Thanks Duane for your answer. It seems like I will be in for some work. I am
try to get the date selection to work. This is what I have and it breaks at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:



I generally create reports based on queries that have little or no criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--



I have a variety of reports that need to be filtered I am not sure of the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should I
do
this to make life easy for the programmer (me), the users, the database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
I think that the assumption is that you are modifying the underlying
query that serves as the recordSource for the report. If you take this
approach, then YES it is not neccessary to pass the WHERE CLAUSE to the
report.
Okay I took the ,,strSql out of the DoCmd line.

What makes no sense is your comment about the Where Clause.

Thanks Fay

:

If you are modifying the SQL property of a saved query, then you shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition, the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after the
"WHERE ".

--
Duane Hookom
MS Access MVP


Thanks Duane for your answer. It seems like I will be in for some work. I
am
try to get the date selection to work. This is what I have and it breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:


I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--


I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
If you're still going to go with this route, I would *HIGHLY* recommend
that you move to a SELECT CASE statement as it will be much easier to
read and later modify.

Select Case Me.txtCriteria
Case "<"
Case ">"
End Select

Additionall the statement CurrentDB.QueryDefs("Query7").SQL = strSQL can
be moved to the end of the Select Case or the If..Thens and placed right
before the DoCmd.OpenReport statement.
I should have also said that I got the report to open this time but the
criterial did not limit the results displayed. I got the whole list of dates.

Current code below:

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview

End Sub

:

If you are modifying the SQL property of a saved query, then you shouldn't
need to pass a WHERE CLAUSE in the DoCmd.OpenReport method. In addition, the
WHERE CLAUSE isn't the full SQL of a query. It is only the stuff after the
"WHERE ".

--
Duane Hookom
MS Access MVP


Thanks Duane for your answer. It seems like I will be in for some work. I
am
try to get the date selection to work. This is what I have and it breaks
at
the last line for the report.

Private Sub txtRosterStart_AfterUpdate()
Dim strSql As Date
Dim stDocName As String

If IsNull(frmDates) Then
MsgBox "No date criteria is selected!", vbInformation
If Me.txtCriteria = "<" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " =#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = ">=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " >=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
ElseIf Me.txtCriteria = "<=" Then
strSql = "SELECT .... WHERE [DateOfClassStart]"
strSql = strSql & " <=#" & Me.txtRosterStart & "#"
CurrentDb.QueryDefs("Query7").SQL = strSql
End If
End If

DoCmd.OpenReport "rptQuery7", acPreview, , strSql


End Sub

:


I generally create reports based on queries that have little or no
criteria.
I use code to create a "where clause" that can be used in the
DoCmd.OpenReport method. For instance rather than have a criteria in a
report's record source like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
I would use code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [SaleDate] >=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [SaleDate] <=#" & Me.txtEnd & "# "
End If
'other similar code to build the where clause
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

I think this method provides a great deal of flexibility.

--
Duane Hookom
MS Access MVP
--


I have a variety of reports that need to be filtered I am not sure of
the
approach to take. Should I create a different query for each type of
filter.
Then use those queries to develop the query for the report? How should
I
do
this to make life easy for the programmer (me), the users, the
database,
and
the future?

Should I create the code as a Public Function and then call it for each
individual report as needed?

Any advise would be appreciated. Thank you. Fay
Click to expand...
Click to expand...
Click to expand...
 
Back
Top