
Signature
Bill McCarthy
wpmATnorthcst.com
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.

Signature
Brendan Reynolds
Access MVP
> In VB code, I execute:
>
[quoted text clipped - 8 lines]
>
> can anyone explain where I've gone wrong? Thanks.
Bill McCarthy - 09 Nov 2006 17:40 GMT
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,
>
[quoted text clipped - 16 lines]
>>
>> can anyone explain where I've gone wrong? Thanks.
Brendan Reynolds - 09 Nov 2006 20:05 GMT
Can you give an example of the value contained in the WhereClause variable
when it fails?

Signature
Brendan Reynolds
Access MVP
> Thanks. Here is the code in it's entirety.
> Note there are 3 places where the WHERECLAUSE is failing.
[quoted text clipped - 67 lines]
>>>
>>> can anyone explain where I've gone wrong? Thanks.
Bill McCarthy - 12 Nov 2006 19:03 GMT
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.
> Can you give an example of the value contained in the WhereClause variable
> when it fails?
[quoted text clipped - 70 lines]
>>>>
>>>> can anyone explain where I've gone wrong? Thanks.
Brendan Reynolds - 12 Nov 2006 21:44 GMT
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'"

Signature
Brendan Reynolds
Access MVP
> here they are. keep in mind, I'm not getting an error message. The WHERE
> clause is simply ignored. Thanks.
[quoted text clipped - 84 lines]
>>>>>
>>>>> can anyone explain where I've gone wrong? Thanks.