Problem with DSum domain

[Cobo]

Hello all,

I have received a horrible legacy DB. It basically is to keep track of sales to clients of products which each has a different tax (referred as IVA).

I am trying to get for each order, the total price BEFORE applying the tax of each group. For example:

Total of $100 @ 4%IVA => $104
Total of $50 @ 10%IVA => $55
TOTAL: $159

There each $100 would be the sum of each article which has an applicable 4% tax(IVA) and $50 would be te sum of all the artices which have an applicable 10% tax(IVA)

I am trying to get those total values through a DSum in the report or the query on which it is based, but I haven't had any success. Whichever Domain I use it always prompts for something when I open the report.

The main query is called "Facturas" and the report is "Factura". I have highlighted the field I've tried doing this in in red.

Any hint on what I should use as a domain in the function would be great.

As it is a bit big (37MB) I have uploaded the Database here: http://melopienso.com/ANNA GOURMET.mdb

Thank you very much!

P.S: Please note that as this is in Spanish, the function is actually called "DSuma" instead of "DSum".

 

[CJ_London]

I would tackle this slightly differently.

In the Facturas query I would add an additional column:

NET: round[PrecioCantidad]/(1+[IVA]) ,2)

And in your report footer, simply have =sum([Net])

You can also simplify splitting out your IVA's using a similar technique

in your query add the following columns

IVA4:iif([IVA]=4,[PrecioCantidad]-[net],0)
IVA10:iif([IVA]=10,[PrecioCantidad]-[net],0)
IVA21:iif([IVA]=21,[PrecioCantidad]-[net],0)

and in your report

for IVA4 - sum([IVA4])
etc

 

[Cobo]

I would tackle this slightly differently.

In the Facturas query I would add an additional column:

NET: round[PrecioCantidad]/(1+[IVA]) ,2)

And in your report footer, simply have =sum([Net])

You can also simplify splitting out your IVA's using a similar technique

in your query add the following columns

IVA4:iif([IVA]=4,[PrecioCantidad]-[net],0)
IVA10:iif([IVA]=10,[PrecioCantidad]-[net],0)
IVA21:iif([IVA]=21,[PrecioCantidad]-[net],0)

and in your report

for IVA4 - sum([IVA4])
etc

Oh my god... THANK YOU so much!

It is incredible how we sometimes complicate our own lives... . That approach is far better and easier. It worked perfectly.

Thanks again, you've saved me some hours and a big headache.

Cheers!

 

[CJ_London]

Pleased to help!