Access throwing an error when using the datevalue() function with a datetime

Question

I am getting an "expression is typed incorrectly, or is is too complex to be evaluated" error when trying to use the datevalue() function with a date/time field in access 2016.

Here is the code for the query i am trying to use.

If I switch the parameters out for actual date values then I get a data mismatch error message when trying to execute the query. Any thoughts on that the issue may be?

SELECT 
      dbo_vTTSTMW_ResourceMileage.[Driver1 ID], 
      dbo_vTTSTMW_ResourceMileage.[Driver1 Name], 
      dbo_vTTSTMW_ResourceMileage.[Order Header Number],
      dbo_vTTSTMW_ResourceMileage.[Total Miles], 
      dbo_vTTSTMW_ResourceMileage.[Empty Miles], 
      dbo_vTTSTMW_ResourceMileage.[Loaded Miles], 
      DateValue([Segment Start Date]) AS [Order Date]
FROM 
      dbo_vTTSTMW_ResourceMileage
GROUP BY 
      dbo_vTTSTMW_ResourceMileage.[Driver1 ID], 
      dbo_vTTSTMW_ResourceMileage.[Driver1 Name], 
      dbo_vTTSTMW_ResourceMileage.[Order Header Number], 
      dbo_vTTSTMW_ResourceMileage.[Total Miles], 
      dbo_vTTSTMW_ResourceMileage.[Empty Miles], 
      dbo_vTTSTMW_ResourceMileage.[Loaded Miles], 
      DateValue([Segment Start Date])
HAVING 
      (((DateValue([Segment Start Date]))>=[start date] And (DateValue([Segment Start Date]))<=[end date]));

Try doing SELECT TOP 1 VarType([Segment Start Date]) AS n FROM dbo_vTTSTMW_ResourceMileage and compare the number with the list here. Is the query returning a "Date value" (VarType value of 7) for that column? If it is returning a String (VarType value of 8) does the string have fractional seconds, e.g., '2017-02-03 11:23:45.789'? — Gord Thompson
So there was an issue with some of the datevalues being null (not sure how that happened but issue for another time). I was able to substitute in a date value for those using the nz() function. however when trying to use the parameters and a value 1/1/2017 and 2/21/2017 the value being returned are not necessarily in range making me think there is another issue with the datevalue. — Tom

Gustav Gustav · Accepted Answer · 2017-02-23T21:35:34

Try specifying the parameters:

PARAMETERS 
    [start date] DateTime,
    [end date] DateTime;
SELECT 
      dbo_vTTSTMW_ResourceMileage.[Driver1 ID], 
      dbo_vTTSTMW_ResourceMileage.[Driver1 Name], 
      dbo_vTTSTMW_ResourceMileage.[Order Header Number],
      dbo_vTTSTMW_ResourceMileage.[Total Miles], 
      dbo_vTTSTMW_ResourceMileage.[Empty Miles], 
      dbo_vTTSTMW_ResourceMileage.[Loaded Miles], 
      DateValue([Segment Start Date]) AS [Order Date]
FROM 
      dbo_vTTSTMW_ResourceMileage
GROUP BY 
      dbo_vTTSTMW_ResourceMileage.[Driver1 ID], 
      dbo_vTTSTMW_ResourceMileage.[Driver1 Name], 
      dbo_vTTSTMW_ResourceMileage.[Order Header Number], 
      dbo_vTTSTMW_ResourceMileage.[Total Miles], 
      dbo_vTTSTMW_ResourceMileage.[Empty Miles], 
      dbo_vTTSTMW_ResourceMileage.[Loaded Miles], 
      DateValue([Segment Start Date])
HAVING 
      (((DateValue([Segment Start Date]))>=[start date] And (DateValue([Segment Start Date]))<=[end date]));

Access throwing an error when using the datevalue() function with a datetime

1 Answers