What function to use ?

  • Thread starter Thread starter Eias
  • Start date Start date
E

Eias

Hi All,
I have a Net Form that records the transactions of the customers.

Date Debit Credit Net
1/1 10 10
1/2 -15 -5
1/2 -20 -25

My question is : what formula do i have to use to calculate the NET field.
This function will take the previous NET into account.
If this is not applicable, how can i do it in a REPORT
Best regards
 
Hi Eias

In a report you can set the ControlSource of your Net textbox to:
=[Debit]+[Credit]

Then set the RunningSum property to "Over All" (or "Over Group" if that is
more appropriate)

In a form it is more difficult. You must use the DSum function to sum all
the fields up to and including the current record. For this you need some
way to uniquely identify the records in the order they are sorted in the
form.

Perhaps you have a TransactionID primary key as well as a TransactionDate
field.
Add a calculated field Net: [Debit]+[Credit] to your form's recordsource
query and sort it by TransactionDate, then TransactionID. Now, set the
ControlSource of your textbox (txtNet) to:
=DSum( "Net", "NameOfRecordsourceQuery", "TransactionDate<=#"
& Format([TransactionDate], "mm/dd/yyyy")
& "# and TransactionID<=" & [TransactionID])
(all this on one line, of course)
 
I do thank you. it works
I have another question ?

Suppose that the customer has more than 1000 transactions. I want to print a
report for him during the last week for example. I want the result to be as
follows for the transactions between 1/1 & 1/3:

Date Debit Credit Net
................................................................. 500
(the net from previous dates)
1/1 10 510
1/2 -15 495
1/2 -20 475

The question again: How to add a field that calculates the NET for the dates
before 1/1 and add it to the NET.

Best wishes


Graham Mandeno said:
Hi Eias

In a report you can set the ControlSource of your Net textbox to:
=[Debit]+[Credit]

Then set the RunningSum property to "Over All" (or "Over Group" if that is
more appropriate)

In a form it is more difficult. You must use the DSum function to sum all
the fields up to and including the current record. For this you need some
way to uniquely identify the records in the order they are sorted in the
form.

Perhaps you have a TransactionID primary key as well as a TransactionDate
field.
Add a calculated field Net: [Debit]+[Credit] to your form's recordsource
query and sort it by TransactionDate, then TransactionID. Now, set the
ControlSource of your textbox (txtNet) to:
=DSum( "Net", "NameOfRecordsourceQuery", "TransactionDate<=#"
& Format([TransactionDate], "mm/dd/yyyy")
& "# and TransactionID<=" & [TransactionID])
(all this on one line, of course)

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Eias said:
Hi All,
I have a Net Form that records the transactions of the customers.

Date Debit Credit Net
1/1 10 10
1/2 -15 -5
1/2 -20 -25

My question is : what formula do i have to use to calculate the NET field.
This function will take the previous NET into account.
If this is not applicable, how can i do it in a REPORT
Best regards
Click to expand...
Click to expand...
 
Hi Eias

Your opening balance can be calculated by summing all the transactions with
a date earlier than the start date.

There are a number of ways to do this:

1. You could use two DSums (Debits and Credits) and add the results together

2. You could write a query to sum the Debit and Credit into a calculated Net
field and then perform a single DSum on that.

3. You could create a SQL string in the Open event of your report to sum the
Debit and Credit fields before the start date and set the value of an
unbound textbox.

Using one of these methods, you will have a textbox displaying the opening
balance. Let's call it txtOpeningBalance.

Now, the trick is to make your running sum textbox (let's call it
txtRunningSum) invisible. Then you have another textbox to display the Net,
with its controlsource set to:
=[txtOpeningBalance] + [txtRunningSum]

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Eias said:
I do thank you. it works
I have another question ?

Suppose that the customer has more than 1000 transactions. I want to print
a
report for him during the last week for example. I want the result to be
as
follows for the transactions between 1/1 & 1/3:

Date Debit Credit Net
................................................................ 500
(the net from previous dates)
1/1 10 510
1/2 -15 495
1/2 -20 475

The question again: How to add a field that calculates the NET for the
dates
before 1/1 and add it to the NET.

Best wishes


Graham Mandeno said:
Hi Eias

In a report you can set the ControlSource of your Net textbox to:
=[Debit]+[Credit]

Then set the RunningSum property to "Over All" (or "Over Group" if that
is
more appropriate)

In a form it is more difficult. You must use the DSum function to sum
all
the fields up to and including the current record. For this you need
some
way to uniquely identify the records in the order they are sorted in the
form.

Perhaps you have a TransactionID primary key as well as a TransactionDate
field.
Add a calculated field Net: [Debit]+[Credit] to your form's recordsource
query and sort it by TransactionDate, then TransactionID. Now, set the
ControlSource of your textbox (txtNet) to:
=DSum( "Net", "NameOfRecordsourceQuery", "TransactionDate<=#"
& Format([TransactionDate], "mm/dd/yyyy")
& "# and TransactionID<=" & [TransactionID])
(all this on one line, of course)

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Eias said:
Hi All,
I have a Net Form that records the transactions of the customers.

Date Debit Credit Net
1/1 10 10
1/2 -15 -5
1/2 -20 -25

My question is : what formula do i have to use to calculate the NET
field.
This function will take the previous NET into account.
If this is not applicable, how can i do it in a REPORT
Best regards
Click to expand...
Click to expand...
Click to expand...
 
Back
Top