jamier01

New Member
Joined
Feb 14, 2005
Messages
10
Hi,

We're currently using Access 2013 to manage some data and it need to reference some other information from SQL Server. This normally isn't an issue.
I've written a view that we can use as a linked table to pull selected data into Access but some users (not all) are getting the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241).

The following query to create the SQL view works for everyone but pulls through all 900k items to the linked table in Access:
SQL:
SELECT linkRef, DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL

What we want is the latest date for each reference (~196k items) so the following should work and does for me and a couple of others but not everyone:
SQL:
SELECT linkRef, MAX(DateUpdated) AS DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL
GROUP BY linkRef

The aggregate seems to cause the error. I've also tried SELECT linkRef, CAST(MAX(DateUpdated) AS DATETIME2(0)) AS DateUpdated as well as SMALLDATETIME. The data type of DateUpdated is DATETIME2(0) so we shouldn't have conversion issues and all dates in this field appear to be valid.

We have an inefficient work-around (this query is only part of a larger group of queries to create a more complicated view but is the cause of our woes) but I'd like to understand why this is happening to prevent it in the future.

Thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,496
Office Version
  1. 365
Platform
  1. Windows
Maybe it does not like the fact that you are naming the Aggregate the same as the original field name (especially since you are also using it in your WHERE clause).
What if you try something like:
Rich (BB code):
SELECT linkRef, MAX(DateUpdated) AS MaxDateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL
GROUP BY linkRef
 

jamier01

New Member
Joined
Feb 14, 2005
Messages
10
Maybe it does not like the fact that you are naming the Aggregate the same as the original field name (especially since you are also using it in your WHERE clause).
Hi Joe, Thanks for the quick reply. I made the change and it has been tested (I can't replicated the error unfortunately) but it didn't solve the issue.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,496
Office Version
  1. 365
Platform
  1. Windows
What we want is the latest date for each reference (~196k items) so the following should work and does for me and a couple of others but not everyone:
So, it is working on some computers but not others?
On a computer where it does work, go to the VB Editor and check with VBA Library References are selected.
Now, go to a computer where it doesn't work and do the same. Are all the same references selected on that computer.
 

jamier01

New Member
Joined
Feb 14, 2005
Messages
10

ADVERTISEMENT

Yes, it works for some people (including me).

There are a few differences with the VBA reference libraries (we were looking at that after a Google, yesterday). My colleague disabled the ones I don’t have and that didn’t change anything. I also enabled the additional references he had but my linked table still works.

There is a difference in the installation – I have 64 bit whereas my colleagues had 32 bit. We upgraded one of my colleagues to 64 bit yesterday and that didn’t have an impact, either.

As we are remotely working (makes diagnostic a bit harder) I also used RDP to work from a colleague’s PC; although under his log on it wouldn’t work, when I looked at the data using my log-on it did. He also RDP’d onto my computer to do the same with the same result (worked for me, not him).

Early on in the diagnostic process, on the SQL Server side I gave the testers db_owner rights (same as me) so I also don't think this is an issue.

I appreciate your time.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,496
Office Version
  1. 365
Platform
  1. Windows
There are a few differences with the VBA reference libraries (we were looking at that after a Google, yesterday). My colleague disabled the ones I don’t have and that didn’t change anything. I also enabled the additional references he had but my linked table still works.
Did you try enabling all the references on their computer (so that they have all the same references as a computer that does work)?

It sounds like you have a lot of variables in play here. That could make it quite difficult to pinpoint the issue.
 

jamier01

New Member
Joined
Feb 14, 2005
Messages
10

ADVERTISEMENT

Good morning. Yes, we matched the VBA references on both computers and that didn't work.

As a trial we also linked to the view from Excel with PowerQuery (we're on Office 2013 so are using the add-in rather than the native get and transform data in later versions). Again, I can pull through all the data but my colleague can't. As you say, there are a lot of variables!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,496
Office Version
  1. 365
Platform
  1. Windows
Yes, without having access to both computers, it is rather difficult to debug.

If you do a Google search on your error message, you can see some other people who had similar problems, and were able to correct them.
I found this thread here: Date conversion error - MS Access front end querying sql back end, and a few people said that removing a certain VBA Reference Library, then adding it back in worked for them.

So I would recommend searching the Web, and see if any of the suggestions help. If you do figure it out, please post back here with your solution.
 

jamier01

New Member
Joined
Feb 14, 2005
Messages
10
Good morning,
To close this off, I discovered that there was a conflict on a data type between SQL and MS Access. MS Access doesn't like the 'BIGINT' data type.
Although this wasn't in the query I was looking at, it was in a parent query that created this data within SQL. I still don't fully understand the reason that the BIGINT was affecting the date but there are multiple conversions between different data sources within the SQL.
Thank you for your help.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,496
Office Version
  1. 365
Platform
  1. Windows
Glad you figured it out, and thanks for reporting it back to us.
 

Forum statistics

Threads
1,144,243
Messages
5,723,206
Members
422,484
Latest member
Yamasaki450

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top