I have 2 pcs in a client's office. One has been upgraded to office 2010. Thus MS Access has been upgraded from 2003 to 2010 on this machine. The two pcs share the same database but have their own ADE file on their respective pc. I have simple report that works in 2003 but does not filter in 2010. The report is a simple transaction report with input boxes for a date range and an account number. In 2010 the report ignores the account number filter which is an on_load event procedure. I get transactions for all accounts for the date range rather then transactions for the account in the filter. The report has no subreports.
I have attached my event procedures below. I am guessing there is some syntax change needed for Access 2010. I have tried a few different methods including defining the filter as a string first but that did not work either. I would appreciate any suggestions.
Option Compare Database
Public dteStart As String, dteEnd As String
Private Sub Report_Activate()
If Len(dteStart) > 0 Then
Me.DateRange = "Statement date: " & Format(dteStart, "mm/dd/yyyy") & " - " & Format(dteEnd, "mm/dd/yyyy")
Me.DateRange = "Statement date: All dates"
Private Sub Report_Load()
If Len(strAcct) > 0 Then
Me.Filter = "AccountNumber = '" & strAcct & "'"
Me.FilterOn = True
Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
dteStart = InputBox("Enter Start Date", "Date Range")
dteEnd = InputBox("Enter End Date", "Date Range")
strAcct = InputBox("Enter Account Number", "Account Filter")
DoCmd.RunSQL "DELETE FROM tbd_Lookup_Reports"
If Len(dteStart) > 0 And dteStart = dteEnd Then
strWhere = "WHERE TransactionDate = '" & dteStart & "'"
ElseIf Len(dteEnd) > 0 Then
strWhere = "WHERE (TransactionDate >= '" & dteStart & "' And TransactionDate <= '" & dteEnd & "')"
DoCmd.RunSQL "Insert Into tbd_Lookup_Reports(LookupID) " & _
"SELECT FinID FROM vwd_Copyright_Income_Statement " & strWhere & " " & _
"GROUP BY FinID"