OpenReport isnt showing first result

[Toadums]

Hi

So I am using a syntax something like:

DateStr = "And [Date] between #" & Me.datePickStart.Value & "# And #" & Me.datePickEnd.Value & "#"

criteria = "ProjectID = '" & frm_button.Text_ProjID.Column(0) & "'" & DateStr

more code...

....

....

DoCmd.OpenReport "rptsql", acViewReport, , criteria

and it works well...except it doesnt include the first Date.

example: if i run it without dates, it works fine, shows ALL of them. if i run it with the start date as say...july 1, it will show all dates from july 2 til the end date. if i run it with the start date as july 2, it will show july 2 and onwards.

it seems like it is only inclusive for the end condition, because the end condition does work well...

any thoughts why it isnt working?

Thanks!!

 

[wiklendt]

rather than "between", i use the "greater than or equal to" (>=) and "less than or equal to" (<=)

here is the date portion of my code when i select between two values to then feed a report.

    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "rptOrders"
    strField = "[OrderDate]"
    
    ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================
    If IsNull(Me.cmbDateStart) Then
        If Not IsNull(Me.cmbDateEnd) Then  'End date, but no start.
            strWhere = strField & " <= " & Format(Me.cmbDateEnd, conDateFormat)
        Else 'neither start nor end dates
               strWhere = ""
        End If
    Else
        If IsNull(Me.cmbDateEnd) Then  'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.cmbDateStart, conDateFormat)
            Else 'Both start and end dates.
                strWhere = strField & " Between " & Format(Me.cmbDateStart, conDateFormat) _
                & " And " & Format(Me.cmbDateEnd, conDateFormat)
        End If
    End If

 

[bulrush]

I agree with Wiklendth. The Between keyword may not include the dates you specify. This is called "all dates between such and such, exclusive".

 

[boblarson]

Between is INCLUSIVE (not exclusive). It includes the dates specified (sorry Bulrush but you're off on that one).

The PROBLEM is that if you have a TIME element included then the Between is like saying

Between xx/xx/xxxx 12:00:00 AM and zz/zz/zzzz 12:00:00 AM

Also, having a field named [DATE] is just asking for trouble because DATE is an Access Reserved word. You should not have ANYTHING named DATE. Rename your field.

I would have to look at the database to determine the real cause of why the first date is not being included, but it is not due to the BETWEEN because it IS inclusive.