Write a code thatcalculates opening balances for previous periods

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please kindly help in how to write a code that calculates opening balances
for previous balance s in MS Access
 
Your question doesn't really provide enough information, but I'm guessing you
want some control on a form to contain the total value of some field in one
of your tables. If so, that would be:

Me![nameofcontrolonform] - DSum("[nameoffieldintable]","nameoftable")

Put this code in the Load event of your form and when it opens the control
will show the sum AT THAT MOMENT IN TIME. However, if you want that value to
adjust itself as you add to or change the numeric values in your table,
you'll need to repeat this code in the AfterUpdate event of the control where
you add/edit the numeric value in question. That way if you increase an
existing 10 to a 15 or create an entirely new record for 25, the control that
shows the total will be updated accordingly.
 
Thank you larry. Your answer is very helpful but my problem is I want to
bring forward the closing balance of one period as opening balance for the
next perid. eg 28 feb 200 pcs as closing balance. the opening balance for
march sholud be 200. I want a code that does this.

Once more I am very grateful for your assistance and continues to seek for
more

Regards

LarryP said:
Your question doesn't really provide enough information, but I'm guessing you
want some control on a form to contain the total value of some field in one
of your tables. If so, that would be:

Me![nameofcontrolonform] - DSum("[nameoffieldintable]","nameoftable")

Put this code in the Load event of your form and when it opens the control
will show the sum AT THAT MOMENT IN TIME. However, if you want that value to
adjust itself as you add to or change the numeric values in your table,
you'll need to repeat this code in the AfterUpdate event of the control where
you add/edit the numeric value in question. That way if you increase an
existing 10 to a 15 or create an entirely new record for 25, the control that
shows the total will be updated accordingly.


jimfan said:
Please kindly help in how to write a code that calculates opening balances
for previous balance s in MS Access
Click to expand...
Click to expand...
 
Okay, basically the same answer (beware, by the way -- I "fatfingered" my
first response, that minus should be an equal sign.)

dim dtStartDate as Date, dtEndDate as Date
dtStartDate = InputBox("Enter start date (dd/mm/yyyy)")
dtEndDate = InputBox("Enter end date (dd/mm/yyyy")

Me![nameofcontrolonform] = DSum("[nameofamountfieldintable]","nameoftable",
"[field with date of records to be summed] >= #" & dtStartDate & "# And
[field with date of records to be summed] <= #" & dtEndDate & "#")

This goes to your data table, gets the amounts for whatever date span you
specified, adds them up, and puts them into the control on your form. If
putting that on a form is not what you want to do, what DO you want to do
with the sum?




jimfan said:
Thank you larry. Your answer is very helpful but my problem is I want to
bring forward the closing balance of one period as opening balance for the
next perid. eg 28 feb 200 pcs as closing balance. the opening balance for
march sholud be 200. I want a code that does this.

Once more I am very grateful for your assistance and continues to seek for
more

Regards

LarryP said:
Your question doesn't really provide enough information, but I'm guessing you
want some control on a form to contain the total value of some field in one
of your tables. If so, that would be:

Me![nameofcontrolonform] - DSum("[nameoffieldintable]","nameoftable")

Put this code in the Load event of your form and when it opens the control
will show the sum AT THAT MOMENT IN TIME. However, if you want that value to
adjust itself as you add to or change the numeric values in your table,
you'll need to repeat this code in the AfterUpdate event of the control where
you add/edit the numeric value in question. That way if you increase an
existing 10 to a 15 or create an entirely new record for 25, the control that
shows the total will be updated accordingly.


jimfan said:
Please kindly help in how to write a code that calculates opening balances
for previous balance s in MS Access
Click to expand...
Click to expand...
Click to expand...
 
If your Regional Settings have the Short Date format as dd/mm/yyyy, that's
not going to work. Access does not respect regional settings in SQL
statements, which is what DSum amounts to. You must use mm/dd/yyyy format in
the SQL (or else an unambiguous format such as yyyy-mm-dd or dd mmm yyyy):

Me![nameofcontrolonform] = DSum("[nameofamountfieldintable]","nameoftable",
"[field with date of records to be summed] Between " & Format(dtStartDate,
"\#yyyy\-mm\-dd\#") & " And " & Format(dtEndDate, "\#yyyy\-mm\-dd\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LarryP said:
Okay, basically the same answer (beware, by the way -- I "fatfingered" my
first response, that minus should be an equal sign.)

dim dtStartDate as Date, dtEndDate as Date
dtStartDate = InputBox("Enter start date (dd/mm/yyyy)")
dtEndDate = InputBox("Enter end date (dd/mm/yyyy")

Me![nameofcontrolonform] =
DSum("[nameofamountfieldintable]","nameoftable",
"[field with date of records to be summed] >= #" & dtStartDate & "# And
[field with date of records to be summed] <= #" & dtEndDate & "#")

This goes to your data table, gets the amounts for whatever date span you
specified, adds them up, and puts them into the control on your form. If
putting that on a form is not what you want to do, what DO you want to do
with the sum?




jimfan said:
Thank you larry. Your answer is very helpful but my problem is I want to
bring forward the closing balance of one period as opening balance for
the
next perid. eg 28 feb 200 pcs as closing balance. the opening balance for
march sholud be 200. I want a code that does this.

Once more I am very grateful for your assistance and continues to seek
for
more

Regards

LarryP said:
Your question doesn't really provide enough information, but I'm
guessing you
want some control on a form to contain the total value of some field in
one
of your tables. If so, that would be:

Me![nameofcontrolonform] -
DSum("[nameoffieldintable]","nameoftable")

Put this code in the Load event of your form and when it opens the
control
will show the sum AT THAT MOMENT IN TIME. However, if you want that
value to
adjust itself as you add to or change the numeric values in your table,
you'll need to repeat this code in the AfterUpdate event of the control
where
you add/edit the numeric value in question. That way if you increase
an
existing 10 to a 15 or create an entirely new record for 25, the
control that
shows the total will be updated accordingly.


:

Please kindly help in how to write a code that calculates opening
balances
for previous balance s in MS Access
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Doug, you're quite right -- brain burp on my part.

Douglas J. Steele said:
If your Regional Settings have the Short Date format as dd/mm/yyyy, that's
not going to work. Access does not respect regional settings in SQL
statements, which is what DSum amounts to. You must use mm/dd/yyyy format in
the SQL (or else an unambiguous format such as yyyy-mm-dd or dd mmm yyyy):

Me![nameofcontrolonform] = DSum("[nameofamountfieldintable]","nameoftable",
"[field with date of records to be summed] Between " & Format(dtStartDate,
"\#yyyy\-mm\-dd\#") & " And " & Format(dtEndDate, "\#yyyy\-mm\-dd\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LarryP said:
Okay, basically the same answer (beware, by the way -- I "fatfingered" my
first response, that minus should be an equal sign.)

dim dtStartDate as Date, dtEndDate as Date
dtStartDate = InputBox("Enter start date (dd/mm/yyyy)")
dtEndDate = InputBox("Enter end date (dd/mm/yyyy")

Me![nameofcontrolonform] =
DSum("[nameofamountfieldintable]","nameoftable",
"[field with date of records to be summed] >= #" & dtStartDate & "# And
[field with date of records to be summed] <= #" & dtEndDate & "#")

This goes to your data table, gets the amounts for whatever date span you
specified, adds them up, and puts them into the control on your form. If
putting that on a form is not what you want to do, what DO you want to do
with the sum?




jimfan said:
Thank you larry. Your answer is very helpful but my problem is I want to
bring forward the closing balance of one period as opening balance for
the
next perid. eg 28 feb 200 pcs as closing balance. the opening balance for
march sholud be 200. I want a code that does this.

Once more I am very grateful for your assistance and continues to seek
for
more

Regards

:

Your question doesn't really provide enough information, but I'm
guessing you
want some control on a form to contain the total value of some field in
one
of your tables. If so, that would be:

Me![nameofcontrolonform] -
DSum("[nameoffieldintable]","nameoftable")

Put this code in the Load event of your form and when it opens the
control
will show the sum AT THAT MOMENT IN TIME. However, if you want that
value to
adjust itself as you add to or change the numeric values in your table,
you'll need to repeat this code in the AfterUpdate event of the control
where
you add/edit the numeric value in question. That way if you increase
an
existing 10 to a 15 or create an entirely new record for 25, the
control that
shows the total will be updated accordingly.


:

Please kindly help in how to write a code that calculates opening
balances
for previous balance s in MS Access
Click to expand...
Click to expand...
Click to expand...
 
Back
Top