including dynamic fields in invoice total

[harvesterlily]

I have created an invoice report. It is grouped by cltcode and billing category. The billing category header determines the caption in the category label. In the detail section the collection total, the percentage rate, and the charges per category are displayed. In the cltcode footer there is a field for legal fees, sales tax and late fees and the sum of category charges(SumofChargeGrand). I am dealing with about 40 clients. The legal fees affect one client, sales tax affects one client and late fees may or may not affect all clients. I need to be able to add these additional fields to the SumOfChargeGrand for an invoice total. I've tried adding a text box and assigning it the value of these fields added together. I keep getting a total of zero. My SumOfChargeGrand will display a value, but it is not included in my invoice total. Please help.

 

[traingamer]

Try using Nz([sales tax]) + Nz([late fees]) , etc. etc.
The Nz function will convert nulls to zeroes so they can be used in arithmetic functions. Nulls are NOT equivalent to zero in those situations.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[harvesterlily]

Thanks traingamer, the problem was in the sum of charges field. I was using IIf statements as the control source for the legal fee and the sales tax. I did use the Nz function for late fees. I'm not sure why, but it is now working. I removed the IIf statements and am assigning the values in the report modue. Access has so many quirks, that for the most part I've given up trying to find out "why". Thanks again for your reply.

 

[dhookom]

I doubt there is any Access 'quirk' involved here but you haven't provided enough details to determine this.

There should be absolutely no reason to use code to total/sum values from your report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[harvesterlily]

Hey Duane,
I'm glad to hear from you. I read your stuff all over the place and am quite impressed with your solutions. As you may have guessed, the "programmer" in my handle was a typo and I'm not sure how to have it corrected. I'm self taught in Access, VBA and SQL. Owing much to the "real" programmers in our company, I can see their creations and modify the concepts to fit my needs. That said I am sure that not all of my designs are fundamentally correct.

I will concede that this was probably not an access quirk, but user error. That doesn't take away that Access does seem to have more than its fair share of issues. Too lengthy a discussion for now.

Related issue:
Date info is used everywhere in this process. Data is stored in directories based on year with subdirectories for month and further subs for each client. Each client has 6 reports output each month. I use global variables to hold the various dates: EndofMonth, BegofMonth, MonthNumber, etc...Some of my report labels use this info: Cash Collections for Jan 2004. All is fine while process is up and running, because variables are set. The info that builds the final version of the invoice remains in the db until it is cleared for processing the next month. If you open the report from the icon, instead of thru the form, the global variables are empty. The value of the global variable is assigned to a private variable within the report. If the global variable is empty I assign it the report variable a value of the calculated date. Currently my results are December 1899. Obviously not the answer for which I was looking. I'm guessing that is Access' default date. I place code in the report_open section. I've set breakpoint to step thru it, but it doesn't stop. Any suggestions

Thanks again

 

[harvesterlily]

Correction to post above: If the global variable is empty I assign the report variable the calculated date. My goal is to have the invoice show the dates in the report if it is open from the icon instead of thru the report. A snp copy of the invoice would be in the clients subdirectory, but I can already see someone opening this report and fussing that the date info is missing.

 

[harvesterlily]

figured it out. placement in reports is everything. my code needed to be in the groupheader format section instead of report open. it is so frustrating to grasp the stuff that should be complicated and spend 4 hrs trying to figure out something so trivial. the devil is in the details. thanks

 

[dhookom]

Glad to hear you got this sorted out.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]