Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    33

    Unanswered: Date comparison not working properly

    I've created a simple access database and everything has been working fine, until the new year (2004).

    I have a simple form, where you enter two dates, and then it uses a report to display any results between those dates.

    The problem is that if my date range falls in 2004, it does not return ANY results. If the date range is in 2003, it works perfectly.

    (for example, 12/01/03 to 12/31/03 WORKS, but 1/1/04 to 1/30/04 does NOT).

    Here is my code on the form:

    Code:
    Option Compare Database
    
    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    
        Dim stDocName As String
        Dim strWhere As String
        
        strWhere = strWhere & " AND satDown=true "
        
        If Len(Me.fromDate & "") > 0 And Len(Me.toDate & "") > 0 Then
            strWhere = strWhere & " AND dateofreferral Between #" & Me.fromDate & "# AND #" & Me.toDate & "#"
            MsgBox ("Between " & Me.fromDate & " and " & Me.toDate)
        Else
            strWhere = strWhere & " AND dateofreferral Between #" & Date & "# AND #" & Date & "#"
        End If
    
        stDocName = "Sits Report"
    
        If Len(strWhere & "") = 0 Then
            DoCmd.OpenReport stDocName, acViewPreview
        Else
            DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=Mid(strWhere, 6)
        End If
    
    
    Exit_Command0_Click:
        Exit Sub
    
    Err_Command0_Click:
        MsgBox Err.Description
        Resume Exit_Command0_Click
        
    End Sub
    Any help is GREATLY appriciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    How do the date text boxes formated? Do you have input masks? I'd format them in the form design view as 'short/long date' or to match formating of table fields.
    I won't byte... hard!

  3. #3
    Join Date
    Oct 2003
    Posts
    33
    I have the two date fields using an input mask (short date I'm pretty sure).

    So, I know the dates are being entered correctly... any idea why it doesn't work? Any errors with my code?

  4. #4
    Join Date
    Oct 2003
    Posts
    33
    Please..... anybody?

  5. #5
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    DON'T use input masks for dates.

    Try this:

    open your form in design view, double click on input/text box used to collect 'From' 'To'dates. When properties pop up go to 'Format' tab. Put the cursor in the top line and click on down arrow on the right. Select 'long/short date'.

    This will enable a user to just put '2 15' in the input/text box and after you go to the next control access will automatically change it to '02-15-2004'.

    Also make sure that table fields that hold dates match the input/text boxs' settings.
    I won't byte... hard!

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try

    "Between #" & CDate( Me.fromDate) & "# AND #" & CDate(Me.toDate) & "#"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •