Code to list 3 years record

[buster497]

Hi,
I am new to this forum.

I have database which holds records dating back 4 years. I am trying to create a code to list all records into report older than 3 years from current date, every time I run the code. The problem is I cant get the date part to work (highlighted in red) . I have tried various date options.
The [datepickedup] is the date.

Can anyone help me on this Pls

Thanks.

Private Sub Form_Load()
Dim intStore As Integer
DoCmd.Maximize

intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] and [datepickedup]< #" & Date & "#- 1095")

'If count of uncomplete jobs is zero display switchboardor
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("You Have " & intStore & " Records In Your System Older Than 3 Years" & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "Record Alert.......") = vbYes Then
DoCmd.OpenReport "ArchiveReport", acViewReport, "", "", acDialog

Else
Exit Sub


End If
End If
End Sub

[June7]

Concatenation and # delimiter not needed when the parameter is Date() function (or Now() as well).

intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] <= Date() - 1095")

Simpler code structure:

Private Sub Form_Load()
Dim intStore As Integer
DoCmd.Maximize

intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] <= Date() - 1095")
'If count of uncomplete jobs is zero display switchboardor
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore > 0 Then
        If MsgBox("You Have " & intStore & " Records In Your System Older Than 3 Years" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "Record Alert.......") = vbYes Then
              DoCmd.OpenReport "ArchiveReport", acViewReport, "", "", acDialog
        End If
End If
End Sub

[buster497]

Hi June7,

Thank you for the reply. I have copied your suggested amendments but the code it is still not working. The code is listing all the records.

Any reason why?.
Thanks

Concatenation and # delimiter not needed when the parameter is Date() function (or Now() as well).

intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] <= Date() - 1095")

Simpler code structure:

Private Sub Form_Load()
Dim intStore As Integer
DoCmd.Maximize

intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] <= Date() - 1095")
'If count of uncomplete jobs is zero display switchboardor
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore > 0 Then
        If MsgBox("You Have " & intStore & " Records In Your System Older Than 3 Years" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "Record Alert.......") = vbYes Then
              DoCmd.OpenReport "ArchiveReport", acViewReport, "", "", acDialog
        End If
End If
End Sub

[June7]

I tested syntax for the DCount with my data and it works.

Have you step debugged? What is the value of intStore when the code runs? Is it correct? If it is then I expect the issue is with report design, not VBA.

Refer to link at bottom of my post for debugging guidelines.

[buster497]

Hi June7.

I have debugged the code and this did not highlight any problems.
i have checked the design and i cant see anything obvious but i am no expert as i am still learning.
I could attach the database but with being new here i don't know how to do this.

[June7]

To provide db follow instructions at bottom of my post.