Finding and replacing help

[cyid]

Ok, I have some expirence with VB and very little with Access. But I was drafted to help my co-worker work on this Access db.

The problem I am having is:

In a table we have cells filled with numbers. When we went to add this up for a total we kep getting errors. Not until we filled the blank cells with zero's did we get a total.

Now we have made a report. We need to add all the cells with numbers in them for a total. But not the cells that contain zero's.

Did I lose ya?

Problem is, if we remove the zero's to get a correct sum in the report, we lose the ability to add the numbers in the cells for a total.

I was thinking about trying to run a command in the form that turned any Nulls into zero's for certain colomns.

Any help or suggestions?

 

[AccessSQLUser]

You don't have to add zeros to the null fields. What you can do is use the nz function which basically converts a null value to any value of your choice. In your case, you would want to convert it to 0. So what you can do is something like the following:

Sum(nz(YourField,0))

 

[cyid]

I tried that

=Sum(nz([PP1980]+[PP1981],0))

in a control source. but it adds up everything. i just want it to add for that row (for each person)

 

[AccessSQLUser]

Oh! I thought you wanted a sum of all records for one field. Now it seems that what you want is to add up 2 fields. So for that you can do:
nz([PP1980],0) + nz([PP1981],0)
Did I understand you correctly?

 

[cyid]

that still adds up the whole colomn. I want just the rows added. Like:

1 0
2 1
0 1

I want row one to equal 1.. but instead it is adding it up to be 3


1 0 Total: 1
2 1 Total: 3
0 1 Total: 1

I need those totals

 

[AccessSQLUser]

Is your report based on a table or query?

 

[cyid]

Right now a table. But I am sure I could make a query.

 

[AccessSQLUser]

Ok. So you have a report that is based on a table. In the table there are 2 fields (among others); one is called PP1980 and the other is PP1981. In your report (make sure it's in the Detail section), you have a text box and its controlsource is set to: nz([PP1980],0) + nz([PP1981],0).
Is this correct so far?

 

[cyid]

Yes.

But now I made a new report/table/form for testing.

It has:
ID
Field1
Field1

With the numbers I supplied above.

On the form and report I have 4 text boxes:
ID
Field1
Field2
Total

The Total textbox has the control source:
=Sum(nz([Field1],0)+nz([Field2],0))

 

[AccessSQLUser]

If you just want 2 total the 2 fields for that one record then leave out the Sum. So are you getting the correct results now?

 

[cyid]

Thank you! I been starting out adding them like that without the sum and without the nz. Then moved on to things like count and sum and all. And when you told me about nz I kept going with sum and count and such. I can be so stupid at times. But I guess a month of working on something I have never done before will do that to a person.