Access Implicit Datatype Conversions

[chunter33]

I was wondering if JET SQL performs any implicit conversions behind the scenes. For example, I've seen a SELECT clause in the past that would select a number field from a table and simultaneously combine it with a text value.

For example:

SELECT Table.number & "TEXT"
FROM Table;

Which would work exactly the same as:

SELECT str(Table.number) & "TEXT"
FROM Table;

But on the other hand if I attempted to perform a JOIN between two tables with fields of different datatypes I would get a type mismatch error.... can somebody kindly explain this to me? Or perhaps point me to a resource where I can find some more information regarding the matter?

 

[lameid]

Fundamentally, the only place Jet is going to do implicit conversions is in the Select statement.... Access will also do this (almost) anyplace else outside a query as well.

In SQL view you should be able to explicitly do the conversion with one of the converstion (c) functions i.e. (clng for Long or cdbl for double etc). This will of course be slow. Str is an older function (going all the way back to BASIC), you should use cstr for conistency.

When JET or any other SQL Engine determines how to execute a SQL statement, it reads the query and develops a query plan to use indexes or any other means to perform the best execution it can figure out (not necessarily the best answer). In order for things like matching, aggregate functions or criteria to happen quickly and efficiently, they have to have the same datatype.

Think of it this way, two elements in the same datatype can talk to each other, two elements of differing datatypes have to drive across town, get a translator, wait for the translation.... So in the case of a language like SQL that is trying to bulk process datasets, it is designed not to waste time with those translators... However Jet is forgiving in Select statements. If however you jump from Jet to SQL Server, you might be bit by SQL Server's datatype precedence.

Some of the above is based on inferences from using the product and understanding how a computer makes comparisons at a low level.