Format query result as Data Type Number, Field Size Decimal

[David]

Hi,

I have a query against an SQL data table column, which is defined as "user
defined" text. These are fields the software vendor allows us to use for our
purposes, and can define as text, numeric, etc.

I want to convert this field to Number, Decimal, Precision 10, 0 decimal
places, in a query, as I want to use this field in a subsequent query to link
to a key field in another table, which is defined as Number, Decimal,
Precision 10, 0 decimal places.

The current issue I have when linking these two fields (User Defined and Key
Field) is the message: "Type Mismatch In Expression".

Thanks in advance.

 

[eselk2003]

Hi,

I have a query against an SQL data table column, which is defined as "user
defined" text. These are fields the software vendor allows us to use for our
purposes, and can define as text, numeric, etc.

I want to convert this field to Number, Decimal, Precision 10, 0 decimal
places, in a query, as I want to use this field in a subsequent query to link
to a key field in another table, which is defined as Number, Decimal,
Precision 10, 0 decimal places.

The current issue I have when linking these two fields (User Defined and Key
Field) is the message: "Type Mismatch In Expression".

Thanks in advance.

Look at CInt(), like:

SELECT CInt([VERSION]) AS VersionAsNumber
FROM CUSTOMER;

Lots of other functions that start with C also, like CStr, CBool,
CDbl, etc...

Anything that can't convert will show up as #error in the results.

 

[David]

That worked well. Thanks!

As an aside, I am surprised that CInt vs. CDec is the solution, as I wanted
to convert it to Decimal.


--
David

Hi,

I have a query against an SQL data table column, which is defined as "user
defined" text. These are fields the software vendor allows us to use for our
purposes, and can define as text, numeric, etc.

I want to convert this field to Number, Decimal, Precision 10, 0 decimal
places, in a query, as I want to use this field in a subsequent query to link
to a key field in another table, which is defined as Number, Decimal,
Precision 10, 0 decimal places.

The current issue I have when linking these two fields (User Defined and Key
Field) is the message: "Type Mismatch In Expression".

Thanks in advance.

Look at CInt(), like:

SELECT CInt([VERSION]) AS VersionAsNumber
FROM CUSTOMER;

Lots of other functions that start with C also, like CStr, CBool,
CDbl, etc...

Anything that can't convert will show up as #error in the results.

 

[John Spencer]

You might want to use something besides CInt - it only has a range of -32,768
to 32,767. Once you exceed that range, you will get errors.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County