NZ functions 1

[drussum]

I have two fields that I am subtracting to equal another field. sometimes one of the two fields will not have any dollar value. What is the syntax for that? both fields are formated for $$
example:
[actual$]-[budget$]= [variance$]

I am currently using: Variance: NZ([actual]-[Budget])and I am getting allot of numbers and decimals.

Thanks in advance for any help.

 

[jebry]

Hi!

Try setting the default value for each field to 0 so that will never be a Null in the field.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[drussum]

Thanks for the suggestion, however the database is an oracle and I dont have the ability to do that. I have to make do with access.

 

[WildHare]

If you're getting more decimal places than you want, you need to simply format the VARIANCE guy. Currency values are stored as two-byte double precisions, I think, so any 'regular' math you do on them will result in very precise results. Just reformat the results.

Variance: Format("####.##", NZ([actual]- [Budget])

or something like that.


Jim Hare
"Remember, you're unique - just like everyone else"
Feel free to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Quehay]

If you can have a negative result the approach would be different...


You should test the fields separately and return 0 rather than using NZ on the outcome of the subtraction. You can't do a mathematical operation with either of the values as NULL, which is what your function is allowing.

So,

Variance: Format(Nz([actual],0)- Nz([Budget],0)),"Currency&quot

 

[WildHare]

Good point, Quehay - both sides should be NZ'ed as you say. Gave you a star...
"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[drussum]

it says "invalid Syntax". There could be negative numbers. in the variance field. How do I get the - sign to show up? (Negative sign)in the colmn with the dollar sign -$100.00

 

[jebry]

Hi!

Quehay had one extra closing parentheses:

Variance: Format(Nz([actual],0)- Nz([Budget],0),"Currency&quot

Should take care of the syntax problem.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[drussum]

Thanks, I am not getting the error anymore. What would the syntax look like if I were using 4 fields to add instead of the two?

 

[drussum]

Scratch the last qestion, Is there a way to have the "-" minus sign show up with in the ()parenthesis?