scotthutchings
Registered User.
- Local time
- Today, 05:53
- Joined
- Mar 26, 2010
- Messages
- 96
I have a procedure that attempts to filter the data depending on the settings set by the user. One of the filters is the date. Before I added this element, my code worked fine but with the addition of my DateFilter, I get an overflow error (#6). What am I doing wrong?
Code:
Me.Refresh
Dim NameFilter As String
Dim DateFilter As String
Dim StartingDate As Date
Dim Est As String
Dim stDocName As String
Dim RecordAccess As String
Dim StartDate As Date
Dim EndDate As Date
Dim Operand As String
RecordAccess = Forms![Bid - Master Form]![RecordAccessRights]
Select Case (Me.optBidAnalysis)
Case 1 'All Estimators
If RecordAccess = "Restricted" Then
MsgBox "You are not authorized to preview this report. Please contact your supervisor.", vbInformation, "Access Restricted"
Exit Sub
Else
NameFilter = ""
Operand = ""
End If
Case 2 'Specify Estimator
If RecordAccess = "Restricted" Then
Est = DLookup("EmployeeShort", "Employee", "Employee ='" & Forms![Bid - Master Form]![txtUserName] & "'")
Else
Est = InputBox("Please enter the estimators initials", "Enter Estimator")
End If
NameFilter = "EmployeeShort = '" & Est & "'"
Operand = " AND "
End Select
Select Case (Me.optBidAnalysisDetail)
Case 1 'Detail
stDocName = "Bid - Result Analysis Detail"
Case 2
stDocName = "Bid - Result Analysis Summary"
End Select
Select Case (Me.optBidAnalysisDates)
Case 1 ' Current year
StartDate = "1/1"
EndDate = Date
Case 2 'custom dates
StartDate = InputBox("Enter the start date", "Start Date", "1/1")
EndDate = InputBox("Enter the end date", "End Date", Date)
End Select
DateFilter = "CurrentBidDate Between #" & StartDate & "# AND #" & EndDate & "#"
Debug.Print NameFilter & Operand & DateFilter
DoCmd.OpenReport stDocName, acViewPreview, , NameFilter & Operand & DateFilter