Dates in SQL Statement

[BJS]

Problem with Dates in SQL statement

I have a button on a form, which has the following code behind it:

If Not IsNull(txtStartDate) Then
If Not strSQLFilter = "" Then
strSQLFilter = strSQLFilter & " AND tblShipments.BLDate Between #" & cboStartDate & "# AND #" & cboFinishDate & "#"
Else
strSQLFilter = strSQLFilter & " tblShipments.BLDate Between #" & cboStartDate & "# AND #" & cboFinishDate & "#"
End If
End If

Currently this sql only retrieves dates between the start and finish dates, but not including them.

I need the sql to include the start and finish dates. I've tried the following with no success:

If Not IsNull(cboStartDate) Then
If Not strSQLFilter = "" Then
strSQLFilter = strSQLFilter & " AND tblShipments.BLDate >= #" & cboStartDate & "# AND tblShipments.BLDate <= #" & cboFinishDate & "#"
Else
strSQLFilter = strSQLFilter & " tblShipments.BLDate >= #" & cboStartDate & "# AND tblShipments.BLDate <= #" & cboFinishDate & "#"
End If
End If

It still doesn't include start and finish dates.

Can anyone help me with this?

Thanks,
BJS

 

[BJS]

Well, I seem to have found away around my problem by using DateSerial to add 1 day to the Finish date. I would still like to know though, how I would do it using >=

Here is how I made it work with DateSerial:

If Not IsNull(txtStartDate) Then
If Not strSQLFilter = "" Then
strSQLFilter = strSQLFilter & " AND tblShipments.BLDate Between #" & cboStartDate & "# AND #" & DateSerial(Year([cboFinishDate]), Month([cboFinishDate]), Day([cboFinishDate] + 1)) & "#"
Else
strSQLFilter = strSQLFilter & " tblShipments.BLDate Between #" & cboStartDate & "# AND #" & DateSerial(Year([cboFinishDate]), Month([cboFinishDate]), Day([cboFinishDate] + 1)) & "#"
End If
End If

 

[ritchieroo]

The BETWEEN operator is inclusive...

Using BETWEEN should give you the correct results, so there must be something odd about your data.

I going to guess that BLDate is entered as a timestamp (using the Now() function).

Because of this, entries made at lunchtime on the on a given date, say 31/12/2003 would actually look something like this...

'2003/12/31 13:12:23'

This value falls after '2003/12/31 00:00:00', so your between statement fails to pick it up. Adding 1 day solves your problem (assuming that no-one enters data at '2004/01/01 00:00:00').

If you don't need a timestamp use the Date() function instead