nz in total expression

[wilpeter]

My Receipts report has seven currency fields, each of which uses a variation on =IIf(

="A",[Amount]*0.4,Null)
The Total field has the formula: nz([DonationA1],0+nz([DonationA2],0+nz([DonationB],0))) (shortened from seven fields).
All but one of the six fields is usually Null, (depending on the [code] value), but sometimes two fields have values.  When these two fields result in a calculated currency amount, the total excludes the first value.
The clue may be that the two that will both have values always appear together and have similar sources, i.e.:
=IIf([code]="A",[Amount]*0.4,Null)
=IIf([code]="A",[Amount]*0.6,Null)
the other five use unique codes, like =IIf([code]="B",[Amount],Null)
Thank you for any suggestions. Cheers, Pete:)

 

[vbaInet]

Your explanations are a bit vague. I don't think you've explained exactly what the problem is.

Please tell us what your objective is and if you can upload some screenshots that would help explain things too.

 

[wilpeter]

I'm sorry, vba but the problem is that the Total formula only shows the 2nd field amount when it should show the total of the first two field amounts, when both have a calculated value. Here's a screen print of the design view of the report. Thanks for your response.

 

[wilpeter]

I doubt the screen print is of any use.
Here is the formula summary:
Luncheon field - =IIf(

="L",[amount]*0.4,Null)
NonDed field - =IIf([code]="L",[amount]*0.6,Null)
The Data Source has a text field called "code" with a single letter in it, that designates the purpose of the "amount" field (currency).  When a Luncheon Ticket is purchased for $50, only $20 is tax-deductible and $30 is non-tax-deductible.  I must show the split, if the code is "L".  If any other code is there, it generates an amount in the other fields of the receipt.  The Total field adds the transferred amount in each of the 7 fields--which normally means it repeats the only amount shown.  It works in all cases except when the two fields end up with $20.00 and $30.00 respectively.
Does that help.  Thanks for your patience.  Pete

 

[wilpeter]

While I was waiting I tried an alternate method that worked.
I added a (non displayed) field called "LuncheonTot" and used that field in the Totalling expression - removing the 40% and 60% amounts. I guess the nz wrap can't handle it the way I presented it. Thanks for your response, though.

 

[vbaInet]

I'm still not following your explanations. The Data Source of what exactly? What are you trying to add up? Is it Lunceon + NonDed?

 

[wilpeter]

The data source is the Table. Each record in the table has both a Code and an amount paid. If the Code is "L" it is a Luncheon Ticket purchase (usually $50.00) of which Canada Revenue counts $20.00 as tax-deductible. If instead the Code is "M" it is a Membership fee. If "R", it is a Course Registration. The Receipt (report) puts the amount paid in its respective position and totals the amounts. Normally the Total Paid is equal to the Membership amount or the Course Registration; but when it is a Luncheon Ticket, it is the total of the $20.00 and the $30.00 - i.e., $50.00
So as not to display Zeroes, I use "Null" in those positions on the report where there is no amount (because that code isn't used). I've put this explanation, in case someone is following this thread. I have a solution that works, so thanks for listening.

 

[vbaInet]

It would help if you show me exactly what you're doing in a spreadsheet so I can how the values add up. I'm still unclear as to your objective because all your posts have mentioned what you're doing but you haven't really said, "My objective is ...". Unless someone else who's done something similar understands and is able to help.

What would be useful is to show me a few records (in a spreadsheet) plus what totals you would like and where.

 

[wilpeter]

My objective was to parallel what I've typed in a spreadsheet for you. Except that in an Access Report, "Null" is used instead of " " in Excel. In Excel, the totals in column I add without regard for " " values; but in Access, you need the nz expression to turn the Null values back to zero. In Access, if one of the values is "Null", the total becomes Null. For some reason, in Access, the total (Payment Recd) ignores the Luncheon Gift Portion when using the Courses+Luncheon+Luncheon2+Donations+Sales+Membership
The actual formula in Access is shown in my earlier (2:36pm) post.
Hope this helps. But, as I said, I've already solved the problem.

 

[vbaInet]

Alright Pete, that makes perfect sense now. You can use a Crosstab query. It requires a tiny bit of complex manipulation and you can see it all in the attached.

Look at qryReceipts.

 

[wilpeter]

I downloaded that mdb file to my downloads folder, but Access says it is "..located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems. To open the file, copy it to your machine or an accessible network location. OK"

 

[wilpeter]

OK, thanks for the help. I copied it to a flash drive and opened it. Not the way I like doing it though. Still, I'll keep it in case I have a situation needing a cross-tab query. I still don't use SQL or VB. Still have 2002 version also. Cheers, mate.

 

[vbaInet]

I still don't use SQL or VB.

It's high time you do. What I showed you there is dynamic, meaning if new fields are added it will take care of that. In your case you will have to code in any new field added.

Happy developing!

 

[wilpeter]

Cheers, vbaInet. I'm 74 this year, but I'll think about upgrading when I get a bit older. BTW, this is just the front end database and it's 92 mb. I'd never have used a crosstab query, however. Appreciated the lesson.

 

[vbaInet]

Pete, you're officialy our most senior citizen on here (I think).

Well done for getting stuck in into technology and we'll be looking out for your posts.

 

[wilpeter]

I don't know about eldest member but still a student. Referring finally back to your CrossTab query, I'm trying to understand it and have used it to demonstrate what I want to accomplish. Query: "Totalling 3" is the cumulation of several attempts. It requires entering the parameter January 18, 2012
My objective is to run such a query to produce a daily report of income from the five sources for each member who made a payment on that day. My Parameter unfortunately became permanent since it wouldn't allow it to be just a flexible fieldname value. As you see, the Totalling didn't happen.
Can't actually use Member as a column heading, since there are 7000 of them and any one might show up on a given day. No panic on this, of course. It's just so I can say I learned how to use CrossTabs. Cheers, Pete.

 

[vbaInet]

Hey Pete,

So your question is you would like me to show you how you can do Summary3 in a crosstab query?

Just fyi, when you put a parameter in a query and then base another query on that parameterised query, the parameter will pop-up when you open this new query.

 

[wilpeter]

The simple answer, of course is to use Analyze in Excel and simply sum in both directions, which gives me totals by member and by income source. This would be done from Summary3, with the parameter "January 18, 2012".
The crosstab query that was called "qryReceipts" had a PayDate by which it was sorted and appeared as a Row Heading. I've tried substituting a field "ReceiptNo" (5 digits) without success, because I still need to use the PayDate (or Payment Recd) for the Where clause. The Crosstab query won't permit a Parameter (such as January 18, 2012) to select the records.
The school processes perhaps 50 in-person or mail transactions per day. Each gets a Receipt for the total (presently handwritten) that summarizes the Membership fees, several courses, donations, rentals, luncheon tickets purchased that day. At the end of the day (before depositing cheques), the day's receipts must be summarized from these paper receipts by the accountant. The Receipt No is also necessary when identifying income tax receipts for the donation portion.
So yes, I'd love to see a total for the row and one row per ReceiptNo retrieved by a date parameter. Thanks hugely. Pete

 

[vbaInet]

Let's get the following questions answered before we can forge ahead:

* Are the Column Headings going to be these fixed values Course, Donations, Luncheon, Gift and Membership?

* Also in your spreadsheet I notice a few things:
1. you no longer have Sales as a Column Heading, I'm guessing the S Code is not needed?
2. What Code does Gift relate to?
3. Can I assume that 'Lunch Deductible' and 'Luncheon Gift Portion' have now been merged into one to make Luncheon?

 

[wilpeter]

Thanks. Wow! We both work Sundays, too?
Course, RegularD, CampaignD, LuncheonG, LuncheonD, Membership, Sales - these would be appropriate titles but meaningless to most - codes R, D, C, G, L, M, & S - (meanings: classroom courses, random donations, campaign donations, luncheon gift expense portion, luncheon tax-ded portion, member fees, and sales/rentals.)
The government requires we separate the luncheon into two portions on Tax Receipts.
As likely non-essential elements: 50 fields in registered courses table, 89 in membership, and 77 in curricula table. 999 course names and several locations.
I was hoping to understand the limitations of crosstab queries, in case they add something. (I converted the database from db2 on DOS to Access 2000 about 8-9 years ago. Haven't billed a penny yet.)