Between And operator gives inconsistent result

[Reaper]

I have a report that is filtered by date using a form with unbound text boxes. The user enters the beginning and ending dates and the report criteria looks at the text boxes for the values of the Between/And operator.



The problem that I am having is that when I enter 1/1/2013 and 1/31/2013 for the dates, any record with a date of 1/31/2013 is not included in the report. However, if I put in 2/1/2013 I get the 1/31/2013 records AND the 2/1/2013 records.

Has anybody seen this or have a solution?

Here is my code:
Private Sub cmdSubmit_Click()

On Error GoTo Err_Handler

If IsNull(Me!dtStartDate.Value) Then
Me!dtStartDate.Value = "1/1/2000"
End If

If IsNull(Me!dtEndDate.Value) Then
Me!dtEndDate.Value = Date + 1
ElseIf Me!dtEndDate.Value = Date Then
Me!dtEndDate.Value = Date + 1
End If

DoCmd.OpenReport "rptreceivinglog", acViewPreview
Exit Sub

Err_Handler:
MsgBox Err.Description, , "Submit Fail"
Exit Sub

End Sub

Code for the BETWEEN/AND operator used in the Report SQL:

Between [Forms]![frmReceivingLogReportDates]![dtStartDate] And [Forms]![frmReceivingLogReportDates]![dtEndDate]

[pbaldy]

My guess would be that your values include a time. That would cause what you're seeing. You can get around it with DateValue().

[qa12dx]

also, exit the dt textbox. do a goto control (any other than the dt fields) before the qry triggers

[alcapps]

Code for the BETWEEN/AND operator used in the Report SQL:

Between [Forms]![frmReceivingLogReportDates]![dtStartDate] And [Forms]![frmReceivingLogReportDates]![dtEndDate][/QUOTE]

I use this sometimes..


Between [Forms]![frmReceivingLogReportDates]![dtStartDate] And Cdate([Forms]![frmReceivingLogReportDates]![dtEndDate] & " 11:59:59 PM")

you add the time componet to the end date unless someone will be adding a record at the last second in the day this will work as well.

hope this helps

[Reaper]

Thanks for the help.
The fix ended up being the CDATE function that alcapps suggested.
Using DATEVALUE() produced the same result. I had already set the format to short date for all the fields.
I guess the time portion is still in play even if all the table and form fields are set to short date.

[pbaldy]

Glad you have a solution. I suspect DateValue() would also have worked. I should have specified that it would be used on the field value. In SQL view:

WHERE DateValue(FieldName) Between ...

[Missinglinq]

I wonder if the reason that CDate() works while DateValue() doesn't is because the OP sometimes is a assigning a String to the dtStartDate Field

Me!dtStartDate.Value = "1/1/2000"

instead of assigning a Date

Me!dtStartDate.Value = #1/1/2000#

to the Field? CDate would convert it to a Date, of course, but DateValue wouldn't.

Linq ;0)>