invoice & vat

[salim64]

Hello,

I'm preparing a simple invoice solution for a friend who owns a small business.

Let me explain briefly what I have done:

An invoice form is based on a query which links clients and invoices tables.
A sub form within invoice is based on a query which links invoice table with sub invoice table and products table. The sub form enables user to enter goods or services.

Now, since this business uses only 2 different VAT rates, queries calculate those rates and later to be called by invoice sub form via Dlookup function.

So far so good.

However, whenever a query returns Null, the relevant field (VAT subtotal) displays #ERROR and therefore, grand total displays #TYPE.

I have tried Nz function with no avail. There must be a way for a query to display 0 when no records are found instead of Null.

I do appreciate any help and Thank you in advance for your attention.

 

[pbaldy]

http://theaccessweb.com/forms/frm0022.htm

 

[salim64]

Thanks paul.

So if I have a field with a control source as the following: =DLookUp("[calculatedtax8]";"invoice_detail_tax8";"[ID]=" & [ID])

How would I use Function nnz ?

Please excuse my ignorance as I don't know how to add a function in Access.

 

[pbaldy]

I thought you were referring to a subform. For that I'd try:

=Nz(DLookUp("[calculatedtax8]";"invoice_detail_tax8";"[ID]=" & [ID]), 0)

 

[salim64]

Yes, Its a field within a subform.

So, I get "The expression you entered contains invalid syntax" and I have doubled checked it.

 

[pbaldy]

Can you attach the db here? I'm not visualizing what you're doing.

 

[salim64]

Its in Turkish; just run fatura form.

VAT = KDV
invoice = fatura

 

[pbaldy]

There's no form with that name. Do you mean the report? Offhand, I see a circular reference there. Text35 refers to Text22, and vice-versa. In the long run, giving your controls meaningful names will make things easier for you.

 

[salim64]

My bad, its called faturalar.

I agree with you regarding field names.

 

[CJ_London]

just butting in and out again

perhaps in Turkish, all commas are semi colons so this

=Nz(DLookUp("[calculatedtax8]";"invoice_detail_tax8";"[ID]=" & [ID]), 0)

should be

=Nz(DLookUp("[calculatedtax8]";"invoice_detail_tax8";"[ID]=" & [ID]);0)

 

[salim64]

Yep. That did it.

Thank you CJ_London for your accurate explanation.

Thank you Paul for your prompt reply.