Calculating Total on Fields with NO data (1 Viewer)

[cindy.endris]

Hi,

My Access instructor said my calculation was straight forward and that I may need to add visual basic coding to initialize the fields (or better word: record) that do not have data. She doesn't know how to do that so she directed me to this website. Do any of you know how to do visual basic coding? If so, can you teach me? I truly appreciate it!

Cheers,
Cindy E.

 

[pbaldy]

Lot's of us do, but I doubt you need it (what good is an instructor that doesn't know the material?). It would help to see your calculation, but if I had to guess, you need the Nz() function to account for Null fields:

Nz(FieldName, 0)

 

[wazz]

not too many people here know visual basic but i'll give it a shot.*

i think what you're looking for is the Nz function. check vba help for specifics. basically it can help you make a field with no data equal to zero (or anything else).


*that was a joke. you came to the right place.

 

[georgedwilkinson]

Hmmm. Are we allowed to see the straight forward calculation? How is it being used (in a form, a query)? Is there some problem with it?

 

[cindy.endris]

Thanks pbaldy!

You can view the original calculation in the attachment. So with your suggestion, should my NEW calculation look like this?

=SumNz([AFLACPrem, 0])+Nz[Benevolence, 0]+...

Thanks again! -Cindy

 

[pbaldy]

Not quite:

=Sum(Nz([AFLACPrem], 0) + Nz([Benevolence], 0) +...)

I would suggest you and your instructor read up on normalization, as the plethora of fields you have should probably be records in a related table. You would only have a record when someone had the item.

 

[cindy.endris]

Thanks for the tip on the formula. I finally got a dollar value instead of nothing BUT it sum all the records instead of per record (per employee). I looked into properties to see if there is something I can click on to sum the total per record but found nothing. Do you have any suggestions? Thanks again for your help!

-Cindy E.

 

[pbaldy]

Where do you have it? It will sum to the level it's in, so in a group footer it should sum the group, in the report footer it will sum it all.

 

[cindy.endris]

I don't know the difference between group and report footer. I created my TOTAL formula on report. I used the fields from the reports in my formula.

When you mentioned footer, I was thinking Page Header, Detail, etc. Well, it's under Detail if that is helpful.

I appreciate your help.

 

[pbaldy]

In the detail section, try it without the Sum() around it.

 

[cindy.endris]

It worked! YOU are AWESOME! YOU ROCK PAUL!!!! Have a great day!

 

[georgedwilkinson]

ItYOU are AWESOME! YOU ROCK PAUL!!!!

Seems like an understatement. Click on Paul's scales to say how much you appreciate him.

 

[pbaldy]

LOL! Glad it worked Cindy, and your thanks are enough for me.

 

[cindy.endris]

Well, I'm glad I made you laugh...even though I wasn't trying to be funny. ha!

When I always think I'm done with the report, I'm not...again. (I know; I don't sound optimistic right now)

I was about to tell my boss that the report is completed UNTIL an employee told me she got married and needs to add her new dependents to her benefits package. Well, to make a long story short. I have a dependent table of 5 dependents and she has a total of 8. I thought to myself, "No problem, I'll just add the fields in the dependents table". That went successful until I click on field list in the Report and it's not listed. How can I get the report to refresh the fields list to include Dependents 6 thru 8?

I hope this all make sense. Thanks!

 

[pbaldy]

I feel the urge to repeat my comment about the design itself, as dependents shouldn't be fields either (normalization). That said, presuming the report is based on a query, you'd have to add the new fields to the query.

Now I'm off to play golf!

 

[cindy.endris]

Normalizing the Dependents table

I know, I know...about the normalization part. I still have the old dependents table where I put only the records that have dependents but the report didn't generate all the dependents on one page. If you could tell me how to do that, that would be super. A programmer told me that sometimes an individual has to denormalize the data to obtain the report you want. If I need to provide more details on how my normalize dependents table looks like, I can.

Thanks for the tip on redoing the query. Hopefully, I won't have to redo the report if I have a new query.