Dsum on a form based from a query to create a opening balance

[sdel_nevo]

Hi All



I hope this is the correct place for me to post this, sorry if it's not.

i have been modifying the access business ledger template and everything is fine, except i cant work out how to put a opening balance amount onto the the report called "Bank Account detailed transactions"

this form is created from the query transactions extended.

the dates to select the transactions from are selected from the form called bank account balances here you can also select which bank account you want to view.

everything works really well, but i can't figure out how to place a opening balance at the top of the report, i currently have this as an unbound text box with the following code in

=DSum("Actual Amount","transactions Extended","Entry Date <= #" & [Forms]![Bank Account Balances]![Start Date Txt Box] & "#")

but i get the#ERROR message when the form opens.


i was hoping that this would sum the value of the "Actual Amount" upto the day before the start date requested and show this in the text box

i have attached the database, if anyone could give me some pointers, that would be awesome.

many thanks

steve

[amrut]

Your query has this criteria for date
Between [Forms]![Bank Account Balances]![Start Date Txt Box] And [Forms]![Bank Account Balances]![End Date Txt Box]
Hence, the query result does not include transactions before [Forms]![Bank Account Balances]![Start Date Txt Box]
Hence, the Dsum will not show any value. Further, the #Error may be due to spaces in criteria. Experts advise against use of spaces in field names too. (Actual Amount)
=DSum("Actual Amount","transactions Extended","Entry Date <= #" & [Forms]![Bank Account Balances]![Start Date Txt Box] & "#")
Try DSum("Actual Amount","transactions Extended","Entry Date<=#" & [Forms]![Bank Account Balances]![Start Date Txt Box] & "#")

[sdel_nevo]

Hi Amrut

Thanks for the reply, I have tried DSum("Actual Amount","transactions Extended","Entry Date<=#" & [Forms]![Bank Account Balances]![Start Date Txt Box] & "#") as suggested but still get the #Error

Steve

[amrut]

This works =Nz(DSum("ActualAmount","transactions Extended","[Entry Date]<=#" & Format([Forms]![Bank Account Balances]![Start Date Txt Box],"mm\/dd\/yyyy") & "#"),0)
after I removed the date criteria in the query and space between Actual Amount. Use the "Where" condition to open the report so that only transactions matching date criteria is included.
Hope this helps.

[sdel_nevo]

Hi Amrut

many thanks for this, not getting the error now, but it's not summing the values for the previous month, the opening balance is always 0

if you set the start date to 01-04-13 and the end date to 04-04-13 amount shown for this month is £25
the opening balance is showing 0

there are transactions in march but it's not summing these, not sure what to put into the where condition, sorry to be a pain but i'm new to access and this is a large learning curve for me.

Steve

[amrut]

Your query has this criteria for date
Between [Forms]![Bank Account Balances]![Start Date Txt Box] And [Forms]![Bank Account Balances]![End Date Txt Box]
Hence, the query result does not include transactions before [Forms]![Bank Account Balances]![Start Date Txt Box]

To check your Dsum, remove the date criteria in the query and then open the report. the opening balance of 245 shows up

[sdel_nevo]

Hi Amrut

many thanks for your help, i put this into the where condition of the search button in vba and it appears to be working

Private Sub Search_Button_Click()


Dim strWhere As String
If Not IsNull(Me.Start_Date_Txt_Box) And Not IsNull(Me.End_Date_Txt_Box) Then
strWhere = "[Entry Date] Between #" & Format(Me.Start_Date_Txt_Box, _
"mm\/dd\/yyyy") & "# And #" & Format(Me.End_Date_Txt_Box, _
"mm\/dd\/yyyy") & "#"
ElseIf Not IsNull(Me.Start_Date_Txt_Box) Then
strWhere = "[Entry Date] = #" & Format(Me.Start_Date_Txt_Box, _
"mm\/dd\/yyyy") & "#"
Else
MsgBox "Please fill in either start and end date or specific date"
Exit Sub
End If
DoCmd.OpenReport ReportName:="Bank Account Detailed transaction List", View:=acViewPreview, _
WhereCondition:=strWhere






End Sub

once again many thanks for your help

Steve

[bukhari]

HI,

can u pls send me email of your solved database. so i can use the commands in my db. thanks

[sdel_nevo]

Hi Bukhari

i can't send you my database is it's not mine to give you, but if you use the attached database in the first post and use the code in subsiquent posts, you should be able to achive what you are looking for

Steve