Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / November 2006

Tip: Looking for answers? Try searching our database.

reports - DoCmd WhereClause stops working

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bill McCarthy - 08 Nov 2006 22:57 GMT
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.

Signature

Bill McCarthy
wpmATnorthcst.com

Brendan Reynolds - 08 Nov 2006 23:09 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.