DateValue

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

Guest

I have query that uses SQL Server 2K as BE. I try to run query in ACCESS with dates as parameters but it is not retreiving records that exceed range of query. Column is set as datetime in table. For example, if date range is Between 5/12/2003 AND 12/31/2003, all 12/31/2003 values are drop because the query excludes any records after 12/31/2003 00:00:01
I try DATEVALUE function in query, but my syntax is not good. Can you please see where my incorrect syntax is. The date range is entered on a form and query is executed on button clic

SELECT dbo_tblCallHistory.CustomerID, dbo_tblCallHistory.CallTime, dbo_tblCallHistory.CallTim
FROM dbo_tblCallHistory
WHERE ((dbo_tblCallHistory.CallTime) Between [Forms]![frmSummaryReports]![txtStartDate] And (DateValue([Forms]![frmSummaryReports]![txtEndDate]))))
 
Problem is that your Calltime includes a time function. You can change the
where statement to something like the following. It will have one problem, in
that it will pick up any call that has a calltime of exactly midnight on the
first of january

WHERE dbo_tblCallHistory.CallTime Between
[Forms]![frmSummaryReports]![txtStartDate] And DateAdd("d",1,[Forms]![frmSummaryReports]![txtEndDate])

More accurate:

WHERE dbo_tblCallHistory.CallTime >= [Forms]![frmSummaryReports]![txtStartDate]
And dbo_tblCallHistory.CallTime < DateAdd("d",1,[Forms]![frmSummaryReports]![txtEndDate])

One other method is to use the Datevalue function as you mentioned in your post.
One problem with it is that is will be slower than the two above queries since
the query has to calculate the DateValue for every row in the table.

WHERE DateValue(dbo_tblCallHistory.CallTime)
Between [Forms]![frmSummaryReports]![txtStartDate]
And [Forms]![frmSummaryReports]![txtEndDate]
 
Back
Top