make values negative based on value in column

[pdtit]

Hey,

The question I have is related to a POS-system :

I have a form and subform which is in fact my sales-entry form in a shop.
I have to types of sales, defined by a character "S" for Sale and "R" for Refund.
S is the regular sale; E means I take back a product from a customer which he didn't like.

For the moment I only use S; know I want to know if I can make the total amount payable calculated based on the salestype (ie. S or R)

In case of "R", the product given back to me is scanned-in, and the amount should be made negative.

In the footer of my form (and report as well) I calculate the total amount payable, total discount,... which should be a total of all "S"-lines on my subform, minus all "R" lines.

If this sounds a bit strange or it isn't really clear, feel free to react and I'll try to explain better.

Regards,

PdtIt

 

[RippleJD]

If I understand you correctly, the bottom line is you want to add up all the "S" transactions and subtract all the "R" transactions on your subform and show the one total on the main form. One possible solution is to create a hidden text box in the footer of your subform that sums your values based on a IIF statement. You can then refer to this text box on your main form.

For example, create a text box called txtTotal in the footer of a subform called MySubform with the following control source:

=Sum(IIf([Type]="S",[amount],IIf([Type]="R",[amount]*-1)))

Then, on your main form (called MyMainForm in this example), set another text box with a control source that refers to the hidden text box on the subform:

=[Forms]![MyMainForm]![MySubform].[Form]![txtTotal]

HTH

JD