Query Access 2007 - ODBC

[Barbara]

Hello all,

I have an ODBC linked table (to an AS/400 server). I created a query (make
table) where I inserted a calculated field to know the volume of a marble
block :
Volume:[mextc]*[mextl]*[mexte]. As these values are in Centimeters I need to
complete this with: Volume:[mextc]*[mextl]*[mexte]/100000
Unfortunatly, in my table, the result is not what I expected! I get 0; 1 ;
5;.. don't understand that!
I checked the properties of my new table and for the new field Volume I get
those properties:
Field Size : Decimal
Precision: 10
Scale:0

I tryed to change this but no big results.
Can someone helpe me. Perhaps, the way I'm doing it is not the proper way!!!!

Thanks for your help.
Barbara

 

[Tom van Stiphout]

On Tue, 15 Jan 2008 05:23:00 -0800, Barbara

You're making several mistakes. The first one is that Volume is a
calculated value, and as such does not belong in a table. Queries that
return Volume can easily calculate it.
Second is that you are mixing units of measure. Even certain Nasa Mars
probe teams could not overcome the difference between inches and
meters, so don't try it yourself. If you have cm, it's best to keep
Volume in cm3.
Third is your formula, where you probably are trying to convert from
cm3 to m3. You should be dividing by 1e6, not 1e5 like you are doing.
Fourth is the precedence rules. Do you really know if the formula will
be evaluated as (a*b*c)/d, or as a*b*(c/d)? No? then use parentheses
to make this explicit. It may have consequences for roundoff.

Decimal is not a well-supported data type in Access. Be careful with
that. I'm sure you can find previous posts in groups.google.com

-Tom.

 

[Barbara]

Hi,
Thanks for your reply but... it doen't really help!

1- "The first one is that Volume is a calculated value, and as such does not
belong in a table. Queries that return Volume can easily calculate it."
My calculated field with the formula is in a Query. This query, when ran,
creates a table. Are you telling me that the calculated fields in these
queries, don't work ? How can I find a manner to get it done!

2 - "Second is that you are mixing units of measure. If you have cm, it's
best to keep
Volume in cm3." !!!!!
No I don't think I am. If I have 3 measures in CM, you don't need a
specific degree to convert those numbers in M!

3 - "Third is your formula: Volume:[mextc]*[mextl]*[mexte]/100000"
Yes you're right. But it was a typing mistake. What I have is :
Volume([mextc]*[mextl]*[mexte])/1000000)
Still don't work!

"Decimal is not a well-supported data type in Access. "
I've noticed that. But can you give me a clue on how or where can I change
that property in the Query?

Thanks but still need help,
Barbara

On Tue, 15 Jan 2008 05:23:00 -0800, Barbara

You're making several mistakes. The first one is that Volume is a
calculated value, and as such does not belong in a table. Queries that
return Volume can easily calculate it.
Second is that you are mixing units of measure. Even certain Nasa Mars
probe teams could not overcome the difference between inches and
meters, so don't try it yourself. If you have cm, it's best to keep
Volume in cm3.
Third is your formula, where you probably are trying to convert from
cm3 to m3. You should be dividing by 1e6, not 1e5 like you are doing.
Fourth is the precedence rules. Do you really know if the formula will
be evaluated as (a*b*c)/d, or as a*b*(c/d)? No? then use parentheses
to make this explicit. It may have consequences for roundoff.

Decimal is not a well-supported data type in Access. Be careful with
that. I'm sure you can find previous posts in groups.google.com

-Tom.

Hello all,

I have an ODBC linked table (to an AS/400 server). I created a query (make
table) where I inserted a calculated field to know the volume of a marble
block :
Volume:[mextc]*[mextl]*[mexte]. As these values are in Centimeters I need to
complete this with: Volume:[mextc]*[mextl]*[mexte]/100000
Unfortunatly, in my table, the result is not what I expected! I get 0; 1 ;
5;.. don't understand that!
I checked the properties of my new table and for the new field Volume I get
those properties:
Field Size : Decimal
Precision: 10
Scale:0

I tryed to change this but no big results.
Can someone helpe me. Perhaps, the way I'm doing it is not the proper way!!!!

Thanks for your help.
Barbara

 

[Tom van Stiphout]

On Tue, 15 Jan 2008 06:24:04 -0800, Barbara

See comments in-line.
-Tom.

Hi,
Thanks for your reply but... it doen't really help!

1- "The first one is that Volume is a calculated value, and as such does not
belong in a table. Queries that return Volume can easily calculate it."
My calculated field with the formula is in a Query. This query, when ran,
creates a table. Are you telling me that the calculated fields in these
queries, don't work ? How can I find a manner to get it done!

They do work, but they are not appropriate since calculated values
don't belong in tables (barring sensible exceptions). Say that you did
include Volume. Then someone comes in and updates the Length field.
Will you remember to recalculate Volume? Under all circumstances,
perhaps outside of your app?
Rather you calculate Volume when you Select from your table.

2 - "Second is that you are mixing units of measure. If you have cm, it's
best to keep
Volume in cm3." !!!!!
No I don't think I am. If I have 3 measures in CM, you don't need a
specific degree to convert those numbers in M!

Granted, but even Nasa engineers can't always get it right.
http://www.cnn.com/TECH/space/9909/30/mars.metric/. One suggestion
would be to include the Unit of Measure in the field name: Length_cm.

3 - "Third is your formula: Volume:[mextc]*[mextl]*[mexte]/100000"
Yes you're right. But it was a typing mistake. What I have is :
Volume([mextc]*[mextl]*[mexte])/1000000)
Still don't work!

Assuming those fields exist, the formula is correct. The outermost
parentheses are not needed. If you temporarily switch it to a Select
query, you can see for yourself.
You don't have Null values, right? All calculations with Null result
in Null.

"Decimal is not a well-supported data type in Access. "
I've noticed that. But can you give me a clue on how or where can I change
that property in the Query?

I don't know if you're trying to create a table in Access or in
AS/400. Assuming the former, one suggestion is that you cast the
result to a Double, which I hope would be enough of a hint to Access
(better: Jet) to create a Double field. Casting is done by wrapping
your expression with CDbl( ). Another suggestion is that you create
the table first, and then fill it using an Append query.

Thanks but still need help,
Barbara

On Tue, 15 Jan 2008 05:23:00 -0800, Barbara

You're making several mistakes. The first one is that Volume is a
calculated value, and as such does not belong in a table. Queries that
return Volume can easily calculate it.
Second is that you are mixing units of measure. Even certain Nasa Mars
probe teams could not overcome the difference between inches and
meters, so don't try it yourself. If you have cm, it's best to keep
Volume in cm3.
Third is your formula, where you probably are trying to convert from
cm3 to m3. You should be dividing by 1e6, not 1e5 like you are doing.
Fourth is the precedence rules. Do you really know if the formula will
be evaluated as (a*b*c)/d, or as a*b*(c/d)? No? then use parentheses
to make this explicit. It may have consequences for roundoff.

Decimal is not a well-supported data type in Access. Be careful with
that. I'm sure you can find previous posts in groups.google.com

-Tom.

Hello all,

I have an ODBC linked table (to an AS/400 server). I created a query (make
table) where I inserted a calculated field to know the volume of a marble
block :
Volume:[mextc]*[mextl]*[mexte]. As these values are in Centimeters I need to
complete this with: Volume:[mextc]*[mextl]*[mexte]/100000
Unfortunatly, in my table, the result is not what I expected! I get 0; 1 ;
5;.. don't understand that!
I checked the properties of my new table and for the new field Volume I get
those properties:
Field Size : Decimal
Precision: 10
Scale:0

I tryed to change this but no big results.
Can someone helpe me. Perhaps, the way I'm doing it is not the proper way!!!!

Thanks for your help.
Barbara

 

[Jamie Collins]

What I have is :
Volume([mextc]*[mextl]*[mexte])/1000000)
Still don't work!

"Decimalis not a well-supported data type in Access. "
I've noticed that. But can you give me a clue on how or where can I change
that property in the Query?

Jet does not support the Standard SQL syntax:

CAST(<cast operand> AS <cast target>)

Instead, it uses the casting functions from the VBA expression
service, despite the fact they generally do not handle the NULL value;
there isn't a one-to-one mapping of Jet data type to VBA casting
function either. VBA does have a 'cast to DECIMAL' function which Jet
could in theory use but it is 'broken' when called via in Jet
(including ACE):

ACC2000: "Wrong Number of Arguments" Error When You Use CDec Function
in a Query
http://support.microsoft.com/kb/225931/en-us

This is often not a problem in practice because decimal values
(between certain limits) are considered natively as being of type
DECIMAL by Jet e.g.

SELECT TYPENAME(0.5);

returns 'Decimal'.

I think the problem you have is that, in Jet, division results in a
value of type FLOAT (a.k.a. Double) unless a value involved is already
of type DECIMAL e.g.

SELECT 4 / 2, TYPENAME (4 / 2);

returns 'Double', whereas

SELECT 4.4 / 2, TYPENAME(4.4 / 2);

returns 'Decimal'.

Further note that arithmetic involving a value of type DECIMAL coerces
the result to type DECIMAL e.g.

SELECT 4 + 0.2 - 0.2, TYPENAME(4 + 0.2 - 0.2);

So, something you could try is, instead of dividing by 1000000,
multiply by 0.000001 e.g.

SELECT 123456789 AS value_integer,
123456789 / 1000000 AS result_double_value,
TYPENAME(123456789 / 1000000) AS result_double_type,
123456789 * 0.000001 AS result_decimal_value,
TYPENAME(123456789 * 0.000001) AS result_decimal_type;

Jamie.

--

 

[Barbara]

Thanks a lot Jamie. It's finally working.

What I have is :
Volume([mextc]*[mextl]*[mexte])/1000000)
Still don't work!

"Decimalis not a well-supported data type in Access. "
I've noticed that. But can you give me a clue on how or where can I change
that property in the Query?

Jet does not support the Standard SQL syntax:

CAST(<cast operand> AS <cast target>)

Instead, it uses the casting functions from the VBA expression
service, despite the fact they generally do not handle the NULL value;
there isn't a one-to-one mapping of Jet data type to VBA casting
function either. VBA does have a 'cast to DECIMAL' function which Jet
could in theory use but it is 'broken' when called via in Jet
(including ACE):

ACC2000: "Wrong Number of Arguments" Error When You Use CDec Function
in a Query
http://support.microsoft.com/kb/225931/en-us

This is often not a problem in practice because decimal values
(between certain limits) are considered natively as being of type
DECIMAL by Jet e.g.

SELECT TYPENAME(0.5);

returns 'Decimal'.

I think the problem you have is that, in Jet, division results in a
value of type FLOAT (a.k.a. Double) unless a value involved is already
of type DECIMAL e.g.

SELECT 4 / 2, TYPENAME (4 / 2);

returns 'Double', whereas

SELECT 4.4 / 2, TYPENAME(4.4 / 2);

returns 'Decimal'.

Further note that arithmetic involving a value of type DECIMAL coerces
the result to type DECIMAL e.g.

SELECT 4 + 0.2 - 0.2, TYPENAME(4 + 0.2 - 0.2);

So, something you could try is, instead of dividing by 1000000,
multiply by 0.000001 e.g.

SELECT 123456789 AS value_integer,
123456789 / 1000000 AS result_double_value,
TYPENAME(123456789 / 1000000) AS result_double_type,
123456789 * 0.000001 AS result_decimal_value,
TYPENAME(123456789 * 0.000001) AS result_decimal_type;

Jamie.