Linked Access tables to SQL server issue (1 Viewer)

Engr. Matthew

New member
Local time
Today, 11:28
Joined
Feb 27, 2021
Messages
3
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.
 

Attachments

  • Screen dump.gif
    Screen dump.gif
    58.5 KB · Views: 252

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
the Date field in MSSQL is String. check that out first.
i think it also has a Millisecs on it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:28
Joined
Oct 29, 2018
Messages
21,357
Hi. What does "did not work" mean? Where you getting an error?
 

Isaac

Lifelong Learner
Local time
Today, 03:28
Joined
Mar 14, 2017
Messages
8,738
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

MVP / VIP
Local time
Today, 10:28
Joined
Jan 14, 2017
Messages
18,186
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

Lifelong Learner
Local time
Today, 03:28
Joined
Mar 14, 2017
Messages
8,738
I think arnel just meant how to delineate it in queries.
 

Engr. Matthew

New member
Local time
Today, 11:28
Joined
Feb 27, 2021
Messages
3
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

AWF VIP
Local time
Today, 10:28
Joined
Jul 26, 2013
Messages
10,353
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.
 

Users who are viewing this thread

Top Bottom