Option Compare Database
Option Explicit
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" [COLOR=Green] 'Do NOT change it to match your local settings.[/COLOR]
[COLOR=Green]'DO set the values in the next 3 lines.
'Put your report name in these quotes.[/COLOR]
strReport = "Input Report"
strDateField = "[Date raised]" [COLOR=Green]'Put your field name in the square brackets in these [/COLOR]quotes
lngView = acViewReport [COLOR=Green] 'Use acViewNormal to print instead of preview.[/COLOR]
[COLOR=Green]'Build the filter string.
'This will check if you have chosen a Date, to construct the Date part filter[/COLOR]
If (IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate)) And _
(Len(Me.txtStartDate & vbNullString) > 0 Or Len(Me.txtEndDate & vbNullString) > 0) Then
strWhere = "(" & strDateField & " BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
Else
[COLOR=Green] 'An error message is returned if Invalid dates are given[/COLOR]
MsgBox "Your have entered an invalid date range (Or) Missed one of the Dates to be filtered for.", vbCritical, "Please check the dates"
Exit Sub
End If
[COLOR=Green]'This will check if you have chosen a Client, to construct the Client filter[/COLOR]
If Me.cboclient.ListIndex <> -1 Or Me.cboclient2.ListIndex <> -1 Then
If strWhere <> vbNullString Then strWhere = strWhere & " AND "
strWhere = strWhere & "(Client = '" & Me.cboclient & "' OR Client = '" & Me.cboclient2 & "')"
End If
[COLOR=Green]'This is to check if the Filter returns any data,
'if it does not have any records. A message is displayed and
'the procedure is exited.[/COLOR]
If DCount("*", "Assets", strWhere) = 0 Then
MsgBox "No data falls between the criteria. Please try again.", vbInformation, "No records"
Exit Sub
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
[COLOR=Green]' Open the report.[/COLOR]
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub