Currency

[sir_dan_mitchell]

Hey again,

Im trying to add up two simple queries. One is [extraoutgoings], the other [TotalVenueCosts].

They are both written as Nz(Sum([etc],0)) and come out correct in the sum calculations individually.

Why then when I try to add say 10 and 40 together do I get 1040. I have tried formatting them into currency, but I get a data type missmatch!

Anyone have any ideas?

Thanks

 

[raskew]

It sounds as though you may be using the '&' operator rather than than the '+' operator. If that's not the fix please post your code.

Bob

 

[Jon K]

Nz(Sum([etc]),0) standing alone returns text strings, not numbers.


To return numbers, you can use Nz(Sum([Etc]))+0

To convert into currency, you can use CCur(Nz(Sum([Etc])))

 

[sir_dan_mitchell]

Hi,

I am using "+" and changing the Nz thing to +0 didnt make a difference it still added like 1040!

How do I find where the code is. I have been into visual basic editor, but it only comes up with code for forms!

Thanks

 

[Mile-O]

Val([Etc]) will ensure your field has a numerical value when adding and not a numerical string for concatenation.

 

[Jon K]

I have attached a Query1 in the attached database that returns text strings and a Query2 for using Nz()+0 to return numbers.

The database was saved from Access 2000.

 

[sir_dan_mitchell]

Val () Worked,

Thank you very much!...again!!

 

[May Parry22]

Nz(Sum([etc]),0) standing alone returns text strings, not numbers.

To return numbers, you can use Nz(Sum([Etc]))+0

You have pointed out the potential pitfall of using Nz([something],0)

I used Nz()+0 and found that it worked.

I also found that to use Val(), we still had to use Nz() to convert the Nulls to string 0s first.