reports - DoCmd WhereClause stops working

[Bill McCarthy]

In VB code, I execute:

DoCmd ReportName, acPreview,,WhereClause

WhereClause is a variable containing a valid statement, like
"Membertype = 5"

I've done this kind of thing a lot in the past and it's always worked. Now,
it has simply stopped working and the WHERE clause is ignored completely.

can anyone explain where I've gone wrong? Thanks.

 

[Brendan Reynolds]

Hi Bill,

This obviously isn't the real code, as you've forgotten to include the call
to the OpenReport method. If you'll copy and paste the real code, someone
may be able to see what the problem is. You may need to include the line
that assigns a value to the WhereClause variable as well as the call to the
OpenReport method.

 

[Bill McCarthy]

Thanks. Here is the code in it's entirety.
Note there are 3 places where the WHERECLAUSE is failing.
This code was COPY/PASTED from another program of mine that works fine and
appropraite changes were made.
I have gotten around this by implementing code in the FORMAT event on the
detail section of the report, but I don't like doing it that way.


Private Sub btnPreview_Click()
Dim stDocName As String
Dim iMonth As Integer
Dim iYear As Integer
Dim iWork As Integer
Dim strg1 As String

On Error GoTo Err_btnPreview_Click

WhereClause = ""
Select Case Frame0.Value
Case 1
stDocName = "Labels Current Members"
Case 2
stDocName = "Renewal Labels"
iMonth = Month(txtDate.Value)
iYear = Year(txtDate.Value)
iWork = iMonth + (12 * iYear)
WhereClause = "ABS(" & Str(iWork) & " - GrossMonths) <= 1"
Case 3
stDocName = "Labels Current Members"
WhereClause = "ShortName = '" & cmbCategory.Value & "'"
Case 4
If IsNothing(cmbGroup.Value) Then
MsgBox "Please enter a GROUP name"
Exit Sub
End If
stDocName = "GroupDonationsSummary"
WhereClause = "StartDate <= #" & txtDate.Value
WhereClause = WhereClause & "# AND ((EndDate IS NULL) OR (EndDate >=
#" & txtDate.Value & "#))"
End Select
'MsgBox WhereClause
DoCmd.OpenReport stDocName, acPreview, , WhereClause

Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox Err.Description
Resume Exit_btnPreview_Click

End Sub ' btnPreview_Click

Hi Bill,

This obviously isn't the real code, as you've forgotten to include the
call to the OpenReport method. If you'll copy and paste the real code,
someone may be able to see what the problem is. You may need to include
the line that assigns a value to the WhereClause variable as well as the
call to the OpenReport method.

 

[Brendan Reynolds]

Can you give an example of the value contained in the WhereClause variable
when it fails?

--
Brendan Reynolds
Access MVP

Thanks. Here is the code in it's entirety.
Note there are 3 places where the WHERECLAUSE is failing.
This code was COPY/PASTED from another program of mine that works fine and
appropraite changes were made.
I have gotten around this by implementing code in the FORMAT event on the
detail section of the report, but I don't like doing it that way.


Private Sub btnPreview_Click()
Dim stDocName As String
Dim iMonth As Integer
Dim iYear As Integer
Dim iWork As Integer
Dim strg1 As String

On Error GoTo Err_btnPreview_Click

WhereClause = ""
Select Case Frame0.Value
Case 1
stDocName = "Labels Current Members"
Case 2
stDocName = "Renewal Labels"
iMonth = Month(txtDate.Value)
iYear = Year(txtDate.Value)
iWork = iMonth + (12 * iYear)
WhereClause = "ABS(" & Str(iWork) & " - GrossMonths) <= 1"
Case 3
stDocName = "Labels Current Members"
WhereClause = "ShortName = '" & cmbCategory.Value & "'"
Case 4
If IsNothing(cmbGroup.Value) Then
MsgBox "Please enter a GROUP name"
Exit Sub
End If
stDocName = "GroupDonationsSummary"
WhereClause = "StartDate <= #" & txtDate.Value
WhereClause = WhereClause & "# AND ((EndDate IS NULL) OR (EndDate >=
#" & txtDate.Value & "#))"
End Select
'MsgBox WhereClause
DoCmd.OpenReport stDocName, acPreview, , WhereClause

Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox Err.Description
Resume Exit_btnPreview_Click

End Sub ' btnPreview_Click

 

[Bill McCarthy]

here they are. keep in mind, I'm not getting an error message. The WHERE
clause is simply ignored. Thanks.

1. ABS(24083 - GrossMonths) <= 1
GrossMonths is a field in the query that is the result of a calculation
similar to :
Month + (Year * 12). I've checked the query and there are values that will
cause this where clause to be successful. I put this same code into the
DETAIL_FORMAT event and it is successful.

2. ShortName = 'FAM'
ShortName is a field in a table and the query.
Like above, the DETAIL_FORMAT event works fine.

 

[Brendan Reynolds]

About the only thing I can suggest at this stage is to try narrowing the
problem down, by trying to create the simplest example that still reproduces
the problem. For example, what happens if you add a command button with the
following code in its Click event to your form?

DoCmd.OpenReport "Labels Current Members", acPreview, , "ShortName =
'FAM'"