rules/guidance for queries to SQL server

InstructionWhich7142

Registered User.
Local time
Today, 22:05
Joined
Feb 24, 2010
Messages
203
I've got an MSSQL 2014 express server that i've started using as data source to interrogate some data from our shift and access control system,

So far it's been a really pain for regional settings etc, especially two key fields for roughly "shift minutes" that are both currency rather than number, these initially all came across as 0 until I disabled the regional setting in the SQL link and also used "SQL Server Native Client 11.0"

However the same two fields also play havoc if an outer join results in them being null in a subsequent query, I get "invalid use of null" etc

I've somewhat bodged around this by using a totals query to kind of "force" Access to handle the data itself rather than poking it back to SQL and getting dumb errors.

However this has now ended up with a load of fields spitting out as text when I've applied an NZ() function to them, even though the null result should be 0, I just don't get it, is there a guide to Access and SQL somewhere? I thought it'd just "GNU" lol
 
I'm now chasing around other ID fields that are long Int in the database, but have had to go through the totals query along with the broken currency fields and are now a mix of text etc, and still don't like being outer joined upon -_-
 
They are probably decimal(x,y) fields in SQL Server.
They are fixed decimal numbers that don't suffer the rounding issues of double or floats.
Access sees them as Currency which is a similar fixed decimal field.

I don't know why the regional settings would be messing with your results? Wha makes you think that?

Also, try using SQL Driver version 17 or later from here

The native client is ancient, and the later version is faster and more robust.
 
I've got an MSSQL 2014 express server that i've started using as data source to interrogate some data from our shift and access control system,

So far it's been a really pain for regional settings etc, especially two key fields for roughly "shift minutes" that are both currency rather than number, these initially all came across as 0 until I disabled the regional setting in the SQL link and also used "SQL Server Native Client 11.0"

However the same two fields also play havoc if an outer join results in them being null in a subsequent query, I get "invalid use of null" etc

I've somewhat bodged around this by using a totals query to kind of "force" Access to handle the data itself rather than poking it back to SQL and getting dumb errors.

However this has now ended up with a load of fields spitting out as text when I've applied an NZ() function to them, even though the null result should be 0, I just don't get it, is there a guide to Access and SQL somewhere? I thought it'd just "GNU" lol
Why is "shift minutes" a currency field? It should be a "number/long integer" field in Access and "int" field in SQL Server. Do you really need to track fractions of a minute for a shift? If you do need to track fractions of a minute, you still should not use a currency field.
 
They are probably decimal(x,y) fields in SQL Server.
They are fixed decimal numbers that don't suffer the rounding issues of double or floats.
Access sees them as Currency which is a similar fixed decimal field.

I don't know why the regional settings would be messing with your results? Wha makes you think that?

Also, try using SQL Driver version 17 or later from here

The native client is ancient, and the later version is faster and more robust.

I had this issue and this was the solution, I was only getting 0 from that field until I changed this, obv all with version 11, I don't know where/why I got that as the latest, maybe it was from the same thread? or I just searched for native client
 
Why is "shift minutes" a currency field? It should be a "number/long integer" field in Access and "int" field in SQL Server. Do you really need to track fractions of a minute for a shift? If you do need to track fractions of a minute, you still should not use a currency field.
Yea, if I knew the developer I'd ask him :) there's no decimals in any of our data 🤷‍♂️
 
They are probably decimal(x,y) fields in SQL Server.
They are fixed decimal numbers that don't suffer the rounding issues of double or floats.
Access sees them as Currency which is a similar fixed decimal field.

I don't know why the regional settings would be messing with your results? Wha makes you think that?

Also, try using SQL Driver version 17 or later from here

The native client is ancient, and the later version is faster and more robust.

Oh, slight problem, I need 32bit not 64bit, the 32bit of version 17 doesn't want to install and the 64bit one doesn't appear in 32bit ODBC :-(
 
Do you have a 32-bit or 64-bit OS ? It's the OS that matters not your version of Access.
Use the 64-bit one if it installs.
 
You may find this video of Maria Barnes' recent presentation to the Access Europe User Group worth watching:
 
Do you have a 32-bit or 64-bit OS ? It's the OS that matters not your version of Access.
Use the 64-bit one if it installs.
ahh yes, it's appeared in ODBC (32-bit) now, for some reason it didn't appear after the reboot but I left the PC on overnight and went to double check today before replying and there it was :-)
 

Users who are viewing this thread

Back
Top Bottom