Sum complications

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in detail section of my report with control sourse as follows:
=(([Cash-VND] & ([BA-176-USD_G]*[ExchRate]) & [BA-305-VND_G] &
([BA-351-USD_G]*[ExchRate]) & [BA-324-VND_G])*-1)

I want to calculate the SUM for that field, but i get the error message
"Data mismatch in criteria expression". What am I doing wrong?

=Sum(Nz((([Cash-VND] & ([BA-176-USD_G]*[ExchRate]) & [BA-305-VND_G] &
([BA-351-USD_G]*[ExchRate]) & [BA-324-VND_G])*-1),0))

I tried to use "+" instead of "&", but it would give "0" result (as out of 5
fields i use in the expression - only 1 is supposed to be not Null)

Can anybody help? I am lost...
Thank you.
Lana
 
Lana said:
It's ok now - somehow after i have removed some "()" - it worked! :)


Lana said:
I have a field in detail section of my report with control sourse as follows:
=(([Cash-VND] & ([BA-176-USD_G]*[ExchRate]) & [BA-305-VND_G] &
([BA-351-USD_G]*[ExchRate]) & [BA-324-VND_G])*-1)

I want to calculate the SUM for that field, but i get the error message
"Data mismatch in criteria expression". What am I doing wrong?

=Sum(Nz((([Cash-VND] & ([BA-176-USD_G]*[ExchRate]) & [BA-305-VND_G] &
([BA-351-USD_G]*[ExchRate]) & [BA-324-VND_G])*-1),0))

I tried to use "+" instead of "&", but it would give "0" result (as out of 5
fields i use in the expression - only 1 is supposed to be not Null)
Click to expand...
Click to expand...


I'm not sure that does work. Removing the wrong ()s will
make the *-1 apply to just the last term, not the entire
expression.

Actually, the use of & in there is inappropriate because of
all the conversions to string and back to numbers, which, I
think, is what causes the problem you posted about. A more
rigorous expression would be:

=-(Nz([Cash-VND], 0) + Nz([BA-176-USD_G]*[ExchRate], 0) +
Nz([BA-305-VND_G], 0) + Nz([BA-351-USD_G]*[ExchRate], 0) +
Nz([BA-324-VND_G], 0))

and the total would be a sum of the same expression.
 
Back
Top