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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
MS Access doesn't like the 'BIGINT' data type.
Not prior to 2016, but it does support BIGINT now?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
L

Legacy 456155

Guest
I just had an issue today with BIGINT. ADO COM converted it to a string. I just went ahead and changed the datatype in the server table column to CHAR(x). Is there another way around this Micron? (Well, besides having to code for it in every instance I use it in my project.)
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
Sorry, that's out of the realm of my expertise. If you're still having this issue with only some users, then I can't see how the same code or expressions that work for somebody else could be at play here. To my mind, it has to be something else - perhaps a difference in Access build versions, or something else that is different from one user to the next. You've already covered Refferences, so it's apparently not that. To me, life is simpler when I grab data from ODBC or other such sources and dump it into native Access tables. Sometimes the data is just coerced, sometimes it needs to be manually converted, but when that is taken care of, everyone's using 'compatible' data.
 
L

Legacy 456155

Guest
It's not a deal breaker in this project. I have a single field in the entire BE that uses BIGINT. Well, it used to. I just cast it from a string to a TempVar(LongLong). I'm just curious if there is some sort of setting in ADO COM or the connection itself. No big deal. I didn't have a plan to fix it, but saw your mention of BIGINT here and thoughtlessly hijacked this thread. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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