Format query result as Data Type Number, Field Size Decimal

D

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.
 
E

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.
 
D

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top