Help with sum() (sql) function with Access 97 DB 1

[meckeard]

Hi all,

I'm trying to run the following sql statement in a vb.net 1.1 windows app and I'm not getting the correct results:

select Nz(sum(Amount)) from tblFeesandPayments where tblFeesandPayments.subID = 21657

What should come back is 0 (zero) but it's returning -1.4210.....

The field "Amount" is a data type Number and the format is set to currency in the Access 97 database. Not sure if that matters.

When I run the same query directly in Access, I get the same results.

Is this something I need to convert or cast in order to get my desired result? If so, how do O accomplish that?

Thanks.

 

[Golom]

Run this

select Amount from tblFeesandPayments 
where tblFeesandPayments.subID = 21657
  AND Amount IS NOT NULL
  AND Amount <> 0

and look at the values. If it's returning -1.4210 then you have fields that are not zero or NULL. The SUM function ignores NULLs.

 

[meckeard]

Golom,

I'm still getting the same results.

When I query the table for all records for that specific subID, there are 3 total and here are the values in the "Amount" field:

(347.80)
280
67.80

This should total 0 and that's what I'm expecting but not getting.

Any other ideas?

Thanks.

 

[Golom]

You said that the Data Type is "Number" and the Format is "Currency". That's a bit different than the data type being "Currency".

What is the "Field Size" for the field (i.e. Single, Double, Decimal, etc.)?

 

[gmmastros]

Golom.

It's a rounding problem. thread796-1471970

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Golom]

Ahhh ... the field is really Single or Double!

That'll do it!

Thanks, George.

 

[meckeard]

The field size is a double.

 

[Golom]

Change the "Data Type" to Currency. That should fix your SUM problem.

 

[gmmastros]

meckeard,

With regards to Access Database issues, I encourage you to trust Golom's advice more than mine.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[meckeard]

Unfortunately, I can't change the database. I'm not the owner of it and this DB has given us so many problems in the past that I don't I'd really want to.

Thanks for all the help guys -- it's really appreciated.

 

[Golom]

More than one way to deal with it

Select Nz(SUM([red]CCur([/red]Amount[red])[/red])) 
From tblFeesandPayments 
Where tblFeesandPayments.subID = 21657