Overflow Error with Date Filter

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
 
What is the result of the Debug.Print?
 
Looks mixed-mode to me. "1/1" is not a valid date. CDate("1/1/10") is a valid date. I'm also not sure what happens in your inputbox when you give it a text default in one case and a date field default in another case. Access is smart... but not always THAT smart.
 
Thank you for the response. The idea of passing "1/1" to the inbox is to select January 1st of the current year. The debug print shows:

ProjectName ='Fire Station' AND CurrentBidDate BETWEEN #1/1/2010# AND #8/9/2010#.

I found the problems was in a record that had a CurrentBidDate but did not have any bid data. I have fixed the query to eliminate this error. Thanks again for the responses.
 

Users who are viewing this thread

Back
Top Bottom