DSum/DLookup problem

B

Bob

I've got a very strange problem. I was using DSum in a report to add up
results over time. Occasionally, the results came back with the correct
magnitude but the wrong sign. Trying to work around this I wrote a separate
query to generate the data I wanted. In this query the signs were correct.
Using DLookup to pull from this new query some of the signs are wrong again.
Any idea what I'm doing to cause this. See Control Source line below.

=DLookUp("[SumReturn]","[qryPctChange]","[qryPctChange].[Ticker] = " &
Chr(34) & [Ticker] & Chr(34))
 
F

fredg

I've got a very strange problem. I was using DSum in a report to add up
results over time. Occasionally, the results came back with the correct
magnitude but the wrong sign. Trying to work around this I wrote a separate
query to generate the data I wanted. In this query the signs were correct.
Using DLookup to pull from this new query some of the signs are wrong again.
Any idea what I'm doing to cause this. See Control Source line below.

=DLookUp("[SumReturn]","[qryPctChange]","[qryPctChange].[Ticker] = " &
Chr(34) & [Ticker] & Chr(34))
Click to expand...

If the query is figuring a percentage change, isn't it possible that
the percentage reflects a loss, not a gain, and that the minus sign is
correct?

Any way, if the actual values are correct, to show the figures as
positive amounts (and ignore the minus sign), wrap the entire
expression in the ABS() function.

=ABS(DLookUp("[SumReturn]","[qryPctChange]","[qryPctChange].[Ticker] =
" &
Chr(34) & [Ticker] & Chr(34)))
 
B

Bob

Nevermind...the field was too small to show the (-) on double digit pct
losses...sorry


fredg said:
I've got a very strange problem. I was using DSum in a report to add up
results over time. Occasionally, the results came back with the correct
magnitude but the wrong sign. Trying to work around this I wrote a separate
query to generate the data I wanted. In this query the signs were correct.
Using DLookup to pull from this new query some of the signs are wrong again.
Any idea what I'm doing to cause this. See Control Source line below.

=DLookUp("[SumReturn]","[qryPctChange]","[qryPctChange].[Ticker] = " &
Chr(34) & [Ticker] & Chr(34))
Click to expand...

If the query is figuring a percentage change, isn't it possible that
the percentage reflects a loss, not a gain, and that the minus sign is
correct?

Any way, if the actual values are correct, to show the figures as
positive amounts (and ignore the minus sign), wrap the entire
expression in the ABS() function.

=ABS(DLookUp("[SumReturn]","[qryPctChange]","[qryPctChange].[Ticker] =
" &
Chr(34) & [Ticker] & Chr(34)))
Click to expand...
 
Top