SpreedSheet into Access, Function Challenge!!!

[George]

Hi,


I'm developing an access database system and the function should much the
existing speedsheet file. The system is for the cash flow of the cash in/out
every day and I will need some help from you guys......

Table:
Branch Date Opening Balance Total Payment Total Collection Closing
Balance
AB 6/9/2007 1000 500 1500
2000

Query/Form:
Branch Date Opening Balance Total Payment Total Collection Closing
Balance
AB 6/9/2007 1000 500 1500
2000
AB 6/10/2007 2000

How can I do this? what ever the closing Balance it will be the Opening
Balance for the next day & user don't have to enter it.

In Excel it will be like this:

A B C D
E F
1 Branch Date Opening Balance Total Payment Total Collection Closing
Balance
2 AB 6/9/2007 1000 500 1500
+C2-D2+E2
3 AB 6/10/2007 F2 500 3000
+C3-D3+E3
4 AB 6/11/2007 F3


TY

 

[Ken Sheridan]

You can either use computed columns in the query underlying the data entry
form or in computed controls on the form. For the former the query would be
along these lines:

SELECT [Branch], [Date],
Nz(DSum("[Total Collection]-[Total Payment]","YourTable","[Date] < #" &
Format([Date],"mm/dd/yyyy") & "#"),0) AS [Opening Balance],
[Total payment], [Total Collection],
Nz(DSum("[Total Collection]-[Total Payment]","YourTable","[Date] <= #" &
Format([Date],"mm/dd/yyyy") & "#"),0) AS [Closing Balance]
FROM [YourTable]
ORDER BY [Date];

To use computed controls on the form rather than computed columns in the
query use the same expressions as above for the ControlSource properties of
the text boxes, i.e.

=Nz(DSum("[Total Collection]-[Total Payment]","[YourTable]","[Date] < #" &
Format([Date],"mm/dd/yyyy") & "#"),0)

and:

=Nz(DSum("[Total Collection]-[Total Payment]","[YourTable]","[Date] <= #" &
Format([Date],"mm/dd/yyyy") & "#"),0)

Note that these should be entered as a single line each in the ControlSource
property; they'll probably have wrapped over two lines in your news reader.

Make sure that the Total payment and Total Collection columns don't allows
Nulls by setting their Required property to True (Yes) in table design and by
setting their DefaultValue properties to 0. Because Null is not a value, but
the absence of a value, an unknown, it propagates in arithmetical
expressions, so the result of any expression containing a Null will be Null
regardless of the value of other operands in the expression.

Be warned that domain aggregate functions are not lightning fast, so you'll
probably notice a performance deficit to some degree.

BTW I'd recommend against using Date as a column name; it could be confused
with the built in Date function in some circumstances and give the wrong
result. If you do use it be sure to wrap it in brackets [like this] when
referencing it. Its better to use an explicit term such as TransactionDate.

Ken Sheridan
Stafford, England