Anomalous results in a calculated field

[Guest]

I have a query which is calculating "quantities" from information in a table.
The calculation includes a private function given to me by George Nicholson
and Nikos Yannacopoulous on 05/11/05 ( Access Database General Questions).
This part works well.

There are 2 fields to each calculation (containing a RoundUpDown function
and IIF function). I then want another query to calculate "costs" by adding
the values of two fields in the first query and multiplying by a value in a
related table.

This appears to work well, EXCEPT if the values of the two fields to be
added in the first query are 10 and 0 in which case the result is the price
multiplied by 100 instead of by 10. I don't know how anomalous (or not) the
results would be if the starting value were multiples of 10. (The values in
these fields will always be either 0 and number or number and 0).

Multiplying only one of the fields at a time results in the correct answer.
I can, of course use one field at a time and add them together later, but
there are 8 fields (1 field, 3 sets of 2 fields and 1 field) and there are 4
other queries with similar ("quantities") values that need to be brought to a
total price, therefore I had been hoping to deal with each quantities query
in 1 field of a "costs" query.

Is it something to do with the way the RoundUpDown private function is
working?

Crazy Lady

 

[John Vinson]

This appears to work well, EXCEPT if the values of the two fields to be
added in the first query are 10 and 0 in which case the result is the price
multiplied by 100 instead of by 10. I don't know how anomalous (or not) the
results would be if the starting value were multiples of 10. (The values in
these fields will always be either 0 and number or number and 0).

Please post the code. It sounds like it's concatenating the values as
strings instead of adding them as numeric values. You might need to
change a line from

..... *([FieldA] + [FieldB]) ....

to .... * (Val([FieldA]) + Val([FieldB]))

to explicitly convert the text string in the textbox to a number.

John W. Vinson[MVP]

 

[Guest]

Hi John

As you may have guessed, it has nothing to do with the number 10 actually,
but if the number (rather than zero) is in the first of the two fields that I
need to add together and multiply.

First the field expressions:

Query1, field 1: RoundUpDown(IIf([field X] between 3 and 7,((([field A]+
[field B])*2)/1.8)*(X-3),0),2)
Query1, field 2: RoundUpDown(IIf([field X] >6,((([field A] + [field B]) *
2)/1.8)*3,0),2)

Note: fields X, A and B being in a table upon which the query is being based.

Query 1 is based on 2 related tables (1 to many) one of which is only
present to provide a criteria for the query to run. However, it returns the
correct answer in both fields.

Query 2, field: ([Query 1]![field1] + [Query 1]![field2])*

![field]

Note: Query 2 is based on the table that has the criteria for query 1 (it
also has the values which are the multiply factor for query 2), and Query 1
(which contains the field by which the previous 2 tables were related).

The Code in the RoundUpDown function is as follows:

Public Function RoundUpDown(varNumber As Variant, varDelta As Variant) As
Variant
'--------------------
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
'Source: Access Advisor Magazine Tips 9/2001
'----------------------------
On Error Resume Next

Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int(((varNumber + (2 * varDelta)) / varDelta) - 1)*
varDelta
End If
End Function

This function would go in a general code module
Then, in your query: MyField=RoundUpDown(YourCalculation, 2)

Thanks,

Crazy Lady

On Mon, 18 Jul 2005 07:17:40 -0700, "Crazy Lady"

This appears to work well, EXCEPT if the values of the two fields to be
added in the first query are 10 and 0 in which case the result is the price
multiplied by 100 instead of by 10. I don't know how anomalous (or not) the
results would be if the starting value were multiples of 10. (The values in
these fields will always be either 0 and number or number and 0).

Please post the code. It sounds like it's concatenating the values as
strings instead of adding them as numeric values. You might need to
change a line from

..... *([FieldA] + [FieldB]) ....

to .... * (Val([FieldA]) + Val([FieldB]))

to explicitly convert the text string in the textbox to a number.

John W. Vinson[MVP]

 

[Guest]

I Still don't know why the anomaly is occuring. It is only when the
RoundUpDown private function is in both fields, however, I have found a way
round it.

In Query 2, instead of Expr: [Query1]![field1] + [Query1]![field
2]*

![field]

I have used

Expr: IIf([Query1]![field1] >0, [Query1]![field1] *

![field], [Query
1]![field 2] *

![field].

It does mean that I am going to have to have an extra layer of queries or
more fields than I would have liked in the interests of elegance, but it
works.

I would still be interested if anyone can come up with the reason the
problem occurred in the first place. - By the way, the RoundUpDown function
is because the result HAS to be an even number

- Crazy Lady

Hi John

As you may have guessed, it has nothing to do with the number 10 actually,
but if the number (rather than zero) is in the first of the two fields that I
need to add together and multiply.

First the field expressions:

Query1, field 1: RoundUpDown(IIf([field X] between 3 and 7,((([field A]+
[field B])*2)/1.8)*(X-3),0),2)
Query1, field 2: RoundUpDown(IIf([field X] >6,((([field A] + [field B]) *
2)/1.8)*3,0),2)

Note: fields X, A and B being in a table upon which the query is being based.

Query 1 is based on 2 related tables (1 to many) one of which is only
present to provide a criteria for the query to run. However, it returns the
correct answer in both fields.

Query 2, field: ([Query 1]![field1] + [Query 1]![field2])*

![field]

Note: Query 2 is based on the table that has the criteria for query 1 (it
also has the values which are the multiply factor for query 2), and Query 1
(which contains the field by which the previous 2 tables were related).

The Code in the RoundUpDown function is as follows:

Public Function RoundUpDown(varNumber As Variant, varDelta As Variant) As
Variant
'--------------------
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
'Source: Access Advisor Magazine Tips 9/2001
'----------------------------
On Error Resume Next

Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int(((varNumber + (2 * varDelta)) / varDelta) - 1)*
varDelta
End If
End Function

This function would go in a general code module
Then, in your query: MyField=RoundUpDown(YourCalculation, 2)

Thanks,

Crazy Lady

On Mon, 18 Jul 2005 07:17:40 -0700, "Crazy Lady"

This appears to work well, EXCEPT if the values of the two fields to be
added in the first query are 10 and 0 in which case the result is the price
multiplied by 100 instead of by 10. I don't know how anomalous (or not) the
results would be if the starting value were multiples of 10. (The values in
these fields will always be either 0 and number or number and 0).

Please post the code. It sounds like it's concatenating the values as
strings instead of adding them as numeric values. You might need to
change a line from

..... *([FieldA] + [FieldB]) ....

to .... * (Val([FieldA]) + Val([FieldB]))

to explicitly convert the text string in the textbox to a number.

John W. Vinson[MVP]