Calculation Value Wrong

[Mike Johnson]

I have the following calculation setup as an expression in a query

((([PresentDays]-[C_O_ADJ_DA])/[BID_DA])-1) where PresentDays = 202,
C_O_ADJ_DA = 32 and BID_DA = 200. The result should be -.15 but I'm getting
-.10

Can anyone tell me why? I've tried setting the format to Standard and
General with the decimal place set to 2.

When I replace the varibles with the actual number values it gives the
correct value.

 

[John Spencer (MVP)]

202 - 32 = 170
170/200 = .85
.85 - 1 = -.15

That is the arithmetic you are performing. So you should be getting the results
you say you expect. Are you sure that the values in the fields are what you say
they are. Have you for testing purposes included the individual values as
columns in the query?

 

[Mike Johnson]

Yes I have. PresentDays is an expression in the query and the other two are
fields that are being displayed. I've tried making two seperate expression
breaking up the expression into two parts but still get the same results.

202 - 32 = 170
170/200 = .85
.85 - 1 = -.15

That is the arithmetic you are performing. So you should be getting the results
you say you expect. Are you sure that the values in the fields are what you say
they are. Have you for testing purposes included the individual values as
columns in the query?

I have the following calculation setup as an expression in a query

((([PresentDays]-[C_O_ADJ_DA])/[BID_DA])-1) where PresentDays = 202,
C_O_ADJ_DA = 32 and BID_DA = 200. The result should be -.15 but I'm getting
-.10

Can anyone tell me why? I've tried setting the format to Standard and
General with the decimal place set to 2.

When I replace the varibles with the actual number values it gives the
correct value.

 

[Dale Fye]

Mike,

I'm with John, the math should work. But there could be a problem because
of the conversion of different data types.

Have you tried:

(((cdbl([PresentDays])-cdbl([C_O_ADJ_DA]))/cdbl([BID_DA]))-1)

How about:

cdbl([PresentDays] - [C_O_ADJ_DA] - [BID_DA]) / cdbl([BID_DA]


Dale

Yes I have. PresentDays is an expression in the query and the other two
are
fields that are being displayed. I've tried making two seperate expression
breaking up the expression into two parts but still get the same results.

202 - 32 = 170
170/200 = .85
.85 - 1 = -.15

That is the arithmetic you are performing. So you should be getting the
results
you say you expect. Are you sure that the values in the fields are what
you say
they are. Have you for testing purposes included the individual values
as
columns in the query?

I have the following calculation setup as an expression in a query

((([PresentDays]-[C_O_ADJ_DA])/[BID_DA])-1) where PresentDays = 202,
C_O_ADJ_DA = 32 and BID_DA = 200. The result should be -.15 but I'm
getting
-.10

Can anyone tell me why? I've tried setting the format to Standard and
General with the decimal place set to 2.

When I replace the varibles with the actual number values it gives the
correct value.

 

[Mike Johnson]

Thanks Dale and John. The first suggestion worked fine once I took care of
Nulls.

(((cdbl([PresentDays])-cdbl([C_O_ADJ_DA]))/cdbl([BID_DA]))-1)

By the way what does the cdbl do? could not find any help on it.

Mike,

I'm with John, the math should work. But there could be a problem because
of the conversion of different data types.

Have you tried:

(((cdbl([PresentDays])-cdbl([C_O_ADJ_DA]))/cdbl([BID_DA]))-1)

How about:

cdbl([PresentDays] - [C_O_ADJ_DA] - [BID_DA]) / cdbl([BID_DA]


Dale

Yes I have. PresentDays is an expression in the query and the other two
are
fields that are being displayed. I've tried making two seperate expression
breaking up the expression into two parts but still get the same results.

202 - 32 = 170
170/200 = .85
.85 - 1 = -.15

That is the arithmetic you are performing. So you should be getting the
results
you say you expect. Are you sure that the values in the fields are what
you say
they are. Have you for testing purposes included the individual values
as
columns in the query?



Mike Johnson wrote:

I have the following calculation setup as an expression in a query

((([PresentDays]-[C_O_ADJ_DA])/[BID_DA])-1) where PresentDays = 202,
C_O_ADJ_DA = 32 and BID_DA = 200. The result should be -.15 but I'm
getting
-.10

Can anyone tell me why? I've tried setting the format to Standard and
General with the decimal place set to 2.

When I replace the varibles with the actual number values it gives the
correct value.

 

[Dale Fye]

MIke,

Glad I could help.

The cdbl( ) conversion function converts a numeric value to a double
precision numeric value. Access, as well as most programming languages
occasionally encounter problems doing math on numeric values that do not
have the same data type. They all do a pretty good job of doing the
conversions, but some number combinations can cause problems. For this
reason, it is best if you can convert the values to the same data type prior
to doing the math.

Dale

Thanks Dale and John. The first suggestion worked fine once I took care of
Nulls.

(((cdbl([PresentDays])-cdbl([C_O_ADJ_DA]))/cdbl([BID_DA]))-1)

By the way what does the cdbl do? could not find any help on it.

Mike,

I'm with John, the math should work. But there could be a problem
because
of the conversion of different data types.

Have you tried:

(((cdbl([PresentDays])-cdbl([C_O_ADJ_DA]))/cdbl([BID_DA]))-1)

How about:

cdbl([PresentDays] - [C_O_ADJ_DA] - [BID_DA]) / cdbl([BID_DA]


Dale

Yes I have. PresentDays is an expression in the query and the other two
are
fields that are being displayed. I've tried making two seperate
expression
breaking up the expression into two parts but still get the same
results.

:

202 - 32 = 170
170/200 = .85
.85 - 1 = -.15

That is the arithmetic you are performing. So you should be getting
the
results
you say you expect. Are you sure that the values in the fields are
what
you say
they are. Have you for testing purposes included the individual
values
as
columns in the query?



Mike Johnson wrote:

I have the following calculation setup as an expression in a query

((([PresentDays]-[C_O_ADJ_DA])/[BID_DA])-1) where PresentDays =
202,
C_O_ADJ_DA = 32 and BID_DA = 200. The result should be -.15 but I'm
getting
-.10

Can anyone tell me why? I've tried setting the format to Standard
and
General with the decimal place set to 2.

When I replace the varibles with the actual number values it gives
the
correct value.