Linked Access tables to SQL server issue

[Engr. Matthew]

Can some one help take a look at this code
strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName" _
& " FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID " & "WHERE (((tblAppointments.AppointDate)>=Date()));"

The SQL string works very well on Access 2016 database, After moving the tables to SQL Server 2014 database the code did not work again.
I have tried my possible best no avail. can anyone there help. you can see the screen dump of the program.

 

[arnelgp]

the Date field in MSSQL is String. check that out first.
i think it also has a Millisecs on it.

 

[theDBguy]

Hi. What does "did not work" mean? Where you getting an error?

 

[Isaac]

1. like dbGuy said, what does "not work" mean? what happens? what do you expect? what do you get?
2. are you running this in a Microsoft Access query, or on SQL Server (i.e., in SQL Server Management Studio, or in a pass-through query in Access)? It makes all the difference to answering the question

 

[isladogs]

the Date field in MSSQL is String. check that out first.
i think it also has a Millisecs on it.

That isn't true. But I think I understand where this comment may have come from...

SQL Server has several date fields including Date (no time component) , DateTime & the newer DateTime2 (which has extra precision - in fact its 0.1 microseconds - 7d.p.). Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Docs

The DateTime datatype has always been identified correctly by Access as a Date/Time field.
However, the old Date datatype was treated as a text field using the very old SQL Server driver - the newer drivers from SQL Server Native Client 11.0 onwards correctly treat it as Date/Time.

However, until recently, Access was unable to handle the newer DateTime2 datatype correctly and that was also treated as a short text (string) field.
That issue was resolved a few months ago with the addition of the Date/Time Extended datatype (in Access 365 only)

 

[Isaac]

I think arnel just meant how to delineate it in queries.

 

[Engr. Matthew]

1. like dbGuy said, what does "not work" mean? what happens? what do you expect? what do you get?
2. are you running this in a Microsoft Access query, or on SQL Server (i.e., in SQL Server Management Studio, or in a pass-through query in Access)? It makes all the difference to answering the question

It does not return the records as usual. I am running it on a link Access query. it is a normal VBA code that build the string and put the result on a text box for use to see the information.

 

[Minty]

As others have said - we suspect this is because Access no longer understands that the date field IS a date field.
It's interpreting it as text due to the data type that is set in the SQL Server.