How can invoicing $0.00 be so difficult?

[Lancer]

I'm making a database to help with my invoicing of miscellaneous jobs but it's proving to be a real pain in the very last stage of development.

Basically, everything is up and working except that when I print a summary sheet of invoices, any invoice which was made for a client where there were no jobs made, the query to generate the invoice summary eliminates that invoice altogether rather than declaring an invoice for that time was made with $0.00 in returns.

In other words, even if I send out an invoice to Fred on 12 June, if that invoice has $0.00 because there were no jobs for Fred (maybe next time), the invoice does not end up on my "invoice summary sheet".

Normally this would be no big deal, except that I ultimately want to give the invoice summaries to a taxman (it proves that I have invoiced clients when I should). Also, for much the same fault, when printing an invoice to a client who has not had fees that term, I get #ERROR on the grand total, rather than $0.00 (hit the "print this invoice" button on the edit invoice form to see what I mean)

The file attached includes a simplified version of what I am talking about. I only hope that someone is capable of helping me on this one.

Please look at the zip attachment for more details (readme.txt), and also for an example database.


Lancer.

 

[neileg]

You are probably having problems with null values. Any kind of calculation that is based on a null will return an error. The Null to Zero function, Nz() will convert nulls to zeros so that the arithmetic remains valid.

 

[Rich]

Why make an invoice for a client if there was no work done?

 

[Lancer]

...because I'm not making the real (bigger) database for myself but for someone else and she's very impulsive. She's the kind of person who might just decide "I think I'll invoice half the clients this month" just to keep them in touch!

...and if those invoices don't show up on the invoice summary, she's going to call it an error, and won't listen to "why do you want to do that?" excuses.

If you looked at the example I attached you'd have noticed where this happens.

I'll look into what that NZ function can do - thanks.

 

[Rich]

Just a thought, why not suggest she sends a statement instead, I'm not sure about down under, but over here the revenue would become highly suspicious of regular invoices for 0 amounts

 

[Lancer]

Yes maybe, I agree with you for my own business practise...

...but then I am making this database for a very difficult customer who's dictating her own business terms. Not me.

Mine is not to question her terms. I'm just supposed to get Access to do what she wants, not argue why I shouldn't. Period.

I can think of another example. I have a "per minute" account with an ISP. They invoice me every month. I don't use them a lot because their lines are slow. I'm used to getting invoices of $0.00. I certainly hope their invoice summaries don't make me disappear.

Please... I don't want to start up a long debate about the business practices of whom I am doing this database for. That would turn into a long read-by-no-one thread and my original question would not get answered. If you are competent enough at making Access do what I am asking, please do tell.

 

[neileg]

If you are competent enough at making Access do what I am asking, please do tell.

Be careful with your use of language. You are asking for valuable adivice and it is being given for free. You are using this advice to help you earn your living. You don't want to offend people with experience and expertise like Rich, otherwise you may find the free advice dries up.

 

[Lancer]

Oh yes. Sorry about that.

I've been working on the main database for a long time now and I've had some of those many late nighters especially of late. I guess I'm starting to sound cranky like I feel. Actually, I'm not really being paid as such for this work, I'm doing it more as a favour for someone, 'though she is getting impatient at this stage (hence, I'm refering to her as a "difficult customer").

I'm 99% complete on this now and should be feeling good aabout it except that I' a bit worn out from pouring over it so much. I think I'll go to bed....best thing.

Sorry for my tone. Please look at the problem if you can. I think the earlier post of the NZ funtion may be a key in cracking this problem.

 

[Rich]

If you are competent enough at making Access do what I am asking, please do tell.
I'd like to think so I've run my business for over twenty years.
I accept what you're trying to say but there is a difference between a monthly statement to a customer and actually storing 0 amounts as an invoice.
Anyway, =Iif(IsNumeric([Forms]![MainForm]![SubForm]![MyControl]),[Forms]![MainForm]![SubForm]![MyControl],0)