Problem with Date code syntax

[Guest]

Could someone tell me what I have wrong with this code in the strwhere
expression? I get a data type mismatch error. (TripStartDate is date/time
format)

Private Sub Command5_Click()

Dim from_date, to_date As Date
Dim strwhere As String

If (IsNull(Me.ReportStartDate)) Or (IsNull(Me.ReportEndDate)) Then

GoTo CheckDateError:

Else

from_date = Me.ReportStartDate
to_date = Me.ReportEndDate

strwhere = "([TripStartDate] between ('" & from_date & "') and ('" & to_date
& "'))"

DoCmd.OpenReport "TripDataReport", acViewPreview, , strwhere
..........

 

[Allen Browne]

Add # as the delimiter around the literal dates in the string.
strwhere = "([TripStartDate] between #" & from_date & _
"# And #" & to_date & "#"

If the software could be used outside the US, it's also a good idea to
format the dates as expected by JET. So what many of us do it to declare a
constant that will format the date correctly and add the hashes, and then
use that throughout your code:

In a standard module:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"
Then in your code:
strwhere = "([TripStartDate] between " & _
Format(Me.ReportStartDate, strcJetDate) & " And " & _
Format(Me.ReportEndDate, strcJetDate)

HTH

 

[Guest]

Thank you. This worked perfectly.

Add # as the delimiter around the literal dates in the string.
strwhere = "([TripStartDate] between #" & from_date & _
"# And #" & to_date & "#"

If the software could be used outside the US, it's also a good idea to
format the dates as expected by JET. So what many of us do it to declare a
constant that will format the date correctly and add the hashes, and then
use that throughout your code:

In a standard module:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"
Then in your code:
strwhere = "([TripStartDate] between " & _
Format(Me.ReportStartDate, strcJetDate) & " And " & _
Format(Me.ReportEndDate, strcJetDate)

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Could someone tell me what I have wrong with this code in the strwhere
expression? I get a data type mismatch error. (TripStartDate is date/time
format)

Private Sub Command5_Click()

Dim from_date, to_date As Date
Dim strwhere As String

If (IsNull(Me.ReportStartDate)) Or (IsNull(Me.ReportEndDate)) Then

GoTo CheckDateError:

Else

from_date = Me.ReportStartDate
to_date = Me.ReportEndDate

strwhere = "([TripStartDate] between ('" & from_date & "') and ('" &
to_date
& "'))"

DoCmd.OpenReport "TripDataReport", acViewPreview, , strwhere