Joining Access table to SQL table with "Float" datatype

[Ken Valenti]

Is there a way to join an Access table to a SQL table that has "Float" as a
datatype?

I am trying to join an Access table to a SQL table - but when I run the
query I get an error "Type mismatch in expression"

The SQL field is of the "Float" datatype (size of 8)- and is something that
I cannot change.

I assume this would be a data Type "Number" in Access, but don't know that
to select for Field Size, etc.

Can this be done - or dos the SQL Data Type need to be changed?

Thanks in advance,

Ken

 

[Rick Brandt]

Is there a way to join an Access table to a SQL table that has
"Float" as a datatype?

I am trying to join an Access table to a SQL table - but when I run
the query I get an error "Type mismatch in expression"

The SQL field is of the "Float" datatype (size of 8)- and is
something that I cannot change.

I assume this would be a data Type "Number" in Access, but don't know
that to select for Field Size, etc.

Can this be done - or dos the SQL Data Type need to be changed?

I would expect Number with size of "Single" or "Double" to work in that
scenario. If not then you might have to join with an expression which is
inefficient.

Actually joining a local table to a linked server table is seldom a good
idea from a performance stand-point. If the local table is very small it
might be alright. Otherwise you could end up pulling that entire SQL table
down so Access can process the join locally.

 

[John W. Vinson]

Is there a way to join an Access table to a SQL table that has "Float" as a
datatype?

I am trying to join an Access table to a SQL table - but when I run the
query I get an error "Type mismatch in expression"

The SQL field is of the "Float" datatype (size of 8)- and is something that
I cannot change.

I assume this would be a data Type "Number" in Access, but don't know that
to select for Field Size, etc.

Can this be done - or dos the SQL Data Type need to be changed?

Thanks in advance,

The matching datatype is Double... BUT!

Not only will performance likely suffer, as Rick says, but you may get
imperfect matches. The Double (Float) datatype is an approximiation, a binary
fraction times an exponent; it's got about 14 decimal places precision, but -
just as the number 1/7 cannot be represented exactly as a decimal number -
many numbers cannot be represented exactly as a binary fraction. It might be
that you would have an Access number that looks like 3.1 and a SQL number that
also looks like 3.1, but they might actually be STORED as 3.09999999999997 and
3.10000000000001, and would not match. I'd be very, very uncomfortable about
using a Float as a key or a linking field; if the value in the float were the
result of a calculation I'd absolutely NOT use it for that purpose.