Solved want sum the taxable item only in total

[ahmedrashed3210]

Hello
I create sample invoice print form and report , want to sum IF it taxable VAT only not Non vat in total vat 5%
sometimes the item is taxable and sometimes not taxable
file in attachment

 

[June7]

=Sum(IIf(VAT = "Vat", Amount, 0)) / 21

If report will produce multiple pages, page number should be in page footer, not report footer.

 

[arnelgp]

see if the computation is correct, and rectify if not.

 

[oleronesoftwares]

=Sum(IIf([vat]="VAT",[amount]*0.05,Null))

 

[ahmedrashed3210]

see if the computation is correct, and rectify if not.

thank you so much , its work

 

[ahmedrashed3210]

thank you guys for your replays
why when i add new item in invoice its automatic make a new record ?

 

[oleronesoftwares]

Yes when you add new item, you are creating a new record in the subform(invoice details)

The main form frminvp number of records remain the same.

You can compare this to adding a new value in a column in excel. on a new row.

 

[June7]

That's the way a data entry form should work. The new record row is available and ready for input and has focus but there is not actually a record in table yet.

 

[oleronesoftwares]

I have looked at the file you sent, the two forms have the same record source, i.e from table called tblinvp.

You need to create a separate table for the subform and create a relationship, i.e one to many relationship between tblinvp. and tblinvpdetails

 

[June7]

Good point.

Lack of invoice info for 2 records is a flaw. Filtering by InvoiceTo will fail, records will be dropped. Invoice 'header' info should be in tblInvp and then fields about invoice items should be in tblInvDetails along with a foreign key field for InvoiceID.

 

[ahmedrashed3210]

can you fix it please because I'm not good in access

 

[June7]

And you won't get better if everyone does these basics for you. The suggested fix is elementary but critical to understanding and working with dbs. Suggest you do the work. Might spend a solid week studying an introductory tutorial book. If you need a structured learning environment to gain skills, take a class.

 

[ahmedrashed3210]

And you won't get better if everyone does these basics for you. The suggested fix is elementary but critical to understanding and working with dbs. Suggest you do the work. Might spend a solid week studying an introductory tutorial book. If you need a structured learning environment to gain skills, take a class.

I'm traying on it , Thank you for your advise

 

[oleronesoftwares]

Done .
See the attachment

You will have to redo the report, but a new table and subform has been added.

 

[ahmedrashed3210]

Done .
See the attachment

You will have to redo the report, but a new table and subform has been added.

Thank you so much for your help
i appreciate that

 

[Cotswold]

@ahmedrashed3210 : you do not do VAT calcs like that. You need a VAT Table of all standard rates, then each of your charge types will have a link to that. When you pull Visa, Hotel or Ticket etc, into an invoice it will know the VAT rate to charge. You then store that calc into the VAT field to each charge line. You then add up the goods field and add up the VAT field for an invoice total.
The standard VAT Table should also have dates for the date range which the VAT Rate is applicable. As the VAT Rates change so will the VAT Table. If the VAT rates change on Dec31st, from January1st you will need to charge at the December rate as you will/can be invoicing Dec charges in Jan. There can be other complications regarding the date at which the Tax Point is set. Is it delivery date or is it invoice date?
VAT unless given some consideration and analysis can be a nightmare if incorrect. With your method if a copy of a Dec invoice is printed next July what VAT rates will it have, if the VAT rate has changed between those dates?

Strictly speaking you should show the VAT% on each line but most just show a code or nothing at all. If there are several VAT rates, 5%, 20%, due to the allocation of one line for one VAT rate, how can your invoice layout show them? Which is why the VAT rate (and/or the VAT charge) is shown on a line by line basis.

I would also advise that the goods line and VAT line totals are stored in your table as pence. (ie using INTEGER CALCS - Longs) in order that the totals on the invoice correspond with the printed values. You then total the pence and string handle the decimal to show correctly in the invoice. If you use decimal totals, using currency, singles or doubles in your Table you can have rounding errors in your totals. Using longs, totals can never be incorrect, even on invoices with a huge number of charge lines. Take a look at SAGE or Pegasus Opera invoices as a guide.