Currency

S

sir_dan_mitchell

Registered User.

Local time

Today, 13:50

Joined

Feb 5, 2002

Messages

74

• Jan 28, 2004

• #1

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

 

R

raskew

AWF VIP

Local time

Today, 07:50

Joined

Jun 2, 2001

Messages

2,734

• Jan 28, 2004

• #2

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

 

J

Jon K

Registered User.

Local time

Today, 13:50

Joined

May 22, 2002

Messages

2,209

• Jan 28, 2004

• #3

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])))

 

S

sir_dan_mitchell

Registered User.

Local time

Today, 13:50

Joined

Feb 5, 2002

Messages

74

• Jan 28, 2004

• #4

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

Back once again...

Local time

Today, 13:50

Joined

Dec 10, 2002

Messages

11,316

• Jan 28, 2004

• #5

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

 

J

Jon K

Registered User.

Local time

Today, 13:50

Joined

May 22, 2002

Messages

2,209

• Jan 28, 2004

• #6

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.

 

Attachments

• nz function access 97.zip

  5.7 KB · Views: 171

Last edited: Jan 28, 2004

S

sir_dan_mitchell

Registered User.

Local time

Today, 13:50

Joined

Feb 5, 2002

Messages

74

• Jan 28, 2004

• #7

Val () Worked,

Thank you very much!...again!!

 

M

May Parry22

New member

Local time

Today, 13:50

Joined

Jan 18, 2004

Messages

7

• Jan 28, 2004

• #8

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

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

Click to expand...

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.