Comparing dates from SQL Server linked table in Access

Question

I have a SQL Server linked table in Access and I am trying to query to extract from a certain date or data ranges (i.e. < 01/31/2017, for example). I tried in the criteria in Access design mode, the following: < #01/31/2017# but it is not comparing correctly.

The field in the SQL server table is datetime data type, I formatted it in Access as Short Date in the properties in design mode.

SELECT dbo_LicensesLiveViewWithRevenue.BAN, 
       dbo_LicensesLiveViewWithRevenue.PTN, 
       dbo_LicensesLiveViewWithRevenue.SOC, 
       dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn, 
       Format([DeactivationDate],"Short Date") AS DeactOn, 
       dbo_LicensesLiveViewWithRevenue.RetailPrice, 
       dbo_LicensesLiveViewWithRevenue.WholeSalePrice, 
       dbo_LicensesLiveViewWithRevenue.AccountID
FROM dbo_LicensesLiveViewWithRevenue
WHERE (((dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn)< #2017/01/31#)) ORDER BY dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn;

I also tried the query with passing the dates as parameters from a form, with no luck.

SELECT dbo_LicensesLiveViewWithRevenue.BAN, 
       dbo_LicensesLiveViewWithRevenue.PTN, 
       dbo_LicensesLiveViewWithRevenue.SOC, 
       dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn, 
       Format([DeactivationDate],"Short Date") AS DeactOn, 
       dbo_LicensesLiveViewWithRevenue.RetailPrice, 
       dbo_LicensesLiveViewWithRevenue.WholeSalePrice, 
       dbo_LicensesLiveViewWithRevenue.AccountID 
FROM dbo_LicensesLiveViewWithRevenue
WHERE (((dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn)<[Forms]![MainForm]![EndDate]));

The results:

When I run the query directly in SQL server is giving me the correct records with correct dates (in SQL the dates are < '20170131'). In Access I have the same count of records but the dates are showing wrong as in the result pictures.

Please show undesired results as it is not comparing correctly and with no luck is not helpful to us. What am I doing wrong? ... we don't even know what's wrong. — Parfait
– Parfait, Commented Sep 20, 2017 at 21:19
Something else is going on. Even same dates - 12/8/2015 - in the first example are not sorted together. So, somehow, you are sorting on some other field as well. Further, field DeactOn is empty which it should not be. — Gustav
– Gustav, Commented Sep 21, 2017 at 7:54

Albert D. Kallal · Accepted Answer · 2017-09-20 22:18:16Z

1

I would suggest that you open the linked table in design view. Ignore the prompt msg about this action being read only).

If the columns in question are newer date time formats (datetime2), and you link using the standard SQL driver, then such date are seen as TEXT columns, and NOT date/time.

A quick look at the linked table in design view will revel if the columns are seen by access as date or text columns.

If you are using any newer format dates from SQL server, you MUST use the newer native 11 (or later) drivers when you link tables to SQL server. It is for this reason that I tend to stick with the older “default” legacy SQL driver. The legacy drivers are installed and included with windows - and thus no install of drivers is required when you run Access on each workstation.

However, if you are using newer date formats in SQL server, then you need to link the tables using the native 11 drivers, and you ALSO have to distribute and install the native 11 drivers on each workstation in addition to Access (or the Access runtime). So while one should prefer and use the newer SQL drivers, the downside is these drivers have to be installed on each workstation.

So double check the data type for those columns that Access shows in table design mode – if they are text, then you need to re-link with the native 11 drivers.

answered Sep 20, 2017 at 22:18

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vanessa Torres Over a year ago

Thank you for your kindness on not voting the answer down. You do not have an idea of the pressure I have in my current project. I just wanted to share my workaround. But because world poverty in the IT industry will be affected I decided to delete my post.

Collectives™ on Stack Overflow

Comparing dates from SQL Server linked table in Access

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related