Upgrade from SQL Server 2008 to 2017 causes date subtraction error on Access front end form using DCount

Question

I inherited supporting a database from an employee that left the company years ago. The team that uses this is being forced to upgrade from SQL Server 2008 to SQL Server 2017.

Everything seems to work fine but there is a form that has text boxes that show some counts of the data using a DCount function. The problem kicks in when it is trying to subtract today's date (using Date()) from a column [DateReceived] (of type smalldatetime in SQL Server) from the table in the DCount function.

This works

=DCount("[ClaimNum]","tblClaimsData","[ClaimStatus] = 'Open' and  #2/4/2021# - [DateReceived] between 45 and 59")

This throws an error

=DCount("[ClaimNum]","tblClaimsData","[ClaimStatus] = 'Open' and Date() - [DateReceived] between 60 and 90")

I cannot seem to figure out a workaround for the Date() function IF that is where the problem is. I have a query that will count the data table but I cannot have a query as a control source for a text box, nor can I get a value of another text box in place of the Date() in my DCount function.

Any thoughts here? Thanks!

ODBC SQL subtraction error

Form with counts - the one that works has the date hard coded in the DCount function

question from:https://stackoverflow.com/questions/66056057/upgrade-from-sql-server-2008-to-2017-causes-date-subtraction-error-on-access-fro

Answer 1

The error message is crystal clear. Your data type is now DateTime2, which the ODBC driver reads as text, causing any numeric date handling to fail.

Best option is to change the data type to DateTime and your code will work.

Else, convert the text dates to true dates:

=DCount("[ClaimNum]","tblClaimsData","[ClaimStatus] = 'Open' and Date() - DateValue([DateReceived]) between 60 and 90")

That may still fail, however, if the text dates contain milliseconds, like 2021-02-04 15:42:30.000.

If so, these must be chopped off before converting.