Problems with date query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings.

I am building a query to return all records between dates selected
by the user.

When I use the following code to build my query, the date selection
works correctly:

stmtSQL = stmtSQL & " AND (DateValue([Alarm Time]) + TimeValue([Alarm
Time]) BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

But when I try to simply things by using the following code, the
query fails.

stmtSQL = stmtSQL & " AND ([Alarm Time] BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

Although the first solution works, it seems a little kludgy to me.
Isn't there a simpler way than splitting apart the DateValue and
TimeValue of [Alarm Time] to make this query work?

Regards,

Charles
 
Charles in Iraq said:
Greetings.

I am building a query to return all records between dates selected
by the user.

When I use the following code to build my query, the date selection
works correctly:

stmtSQL = stmtSQL & " AND (DateValue([Alarm Time]) + TimeValue([Alarm
Time]) BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

But when I try to simply things by using the following code, the
query fails.

stmtSQL = stmtSQL & " AND ([Alarm Time] BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

Although the first solution works, it seems a little kludgy to me.
Isn't there a simpler way than splitting apart the DateValue and
TimeValue of [Alarm Time] to make this query work?
Click to expand...

Date values in Access queries need to be delimited with #, not quotes.

stmtSQL = stmtSQL & " AND ([Alarm Time] BETWEEN #" _
& Me.dtpFromDate & "# AND #" _
& Me.dtpToDate & "#)"
 
If your date/time values include non-zero times of day, as looks like might
be the case here, a BETWEEN….AND operation will not return any rows on the
final day of the range if they have a non-zero time of day, i.e. after
midnight at the start of the day. You can handle this by defining the range
differently. Also, it might be prudent to internationalize the query by
making sure the date literals are formatted in US date format regardless of
the system date format:

stmtSQL = stmtSQL & _
" AND [Alarm Time] >= #" & _
FORMAT(Me.dtpFromDate,"mm/dd/yyyy") & _
"# AND [Alarm Time] < #" & _
FORMAT(Me.dtpFromDate,"mm/dd/yyyy") & "# +1"

Ken Sheridan
Stafford, England
 
Thanks. That worked.

Rick Brandt said:
Charles in Iraq said:
Greetings.

I am building a query to return all records between dates selected
by the user.

When I use the following code to build my query, the date selection
works correctly:

stmtSQL = stmtSQL & " AND (DateValue([Alarm Time]) + TimeValue([Alarm
Time]) BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

But when I try to simply things by using the following code, the
query fails.

stmtSQL = stmtSQL & " AND ([Alarm Time] BETWEEN '" _
& Me.dtpFromDate & "' AND '" _
& Me.dtpToDate & "')"

Although the first solution works, it seems a little kludgy to me.
Isn't there a simpler way than splitting apart the DateValue and
TimeValue of [Alarm Time] to make this query work?
Click to expand...

Date values in Access queries need to be delimited with #, not quotes.

stmtSQL = stmtSQL & " AND ([Alarm Time] BETWEEN #" _
& Me.dtpFromDate & "# AND #" _
& Me.dtpToDate & "#)"
Click to expand...
 
Back
Top