Check Register Database - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Check Register Database

Paul Maguire

4 years ago

Hi I recently purchased the Check Register database seminar and I have been trying to customise one part of it and I cannot seem to work it out. When I select one of my accounts in the ListBox on the CheckRegMenuF form, the CheckRegF form opens and displays the list of records for that account. I am looking to tweak the RunningBalance field to display the running total for JUST the records displayed for that account and the selected date period/ Can anyone please assist?? Many thanks in advance

	Adam Schwanz @Reply 4 years ago
If you just put a textbox into the row and put `=DSum("Credit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate])-DSum("Debit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate])` as the control source it should do what you want.

Paul Maguire OP @Reply

4 years ago

Thank you Adam for responding so quickly, but that is just returning #Error in the text box. As it stands the RunningBalance field calculates the balance for all the records in the table, regardless of the AccountID or the date period between the unbound textbox StartDate and the EndDate textbox. I am trying to calculate the RunningBalance for the records displayed from the account selected and for the period selected.

	Adam Schwanz @Reply 4 years ago
Put a date in the startdate field and it should return a value. Change to this to limit account. `=DSum("Credit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and AccountID=" & AccountCombo)-DSum("Debit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and AccountID=" & AccountCombo)`

	Adam Schwanz @Reply 4 years ago
if you're limiting the top end to I guess we have to add that as well... Do this `=DSum("Credit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)-DSum("Debit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)`

	Paul Maguire OP @Reply 4 years ago
Adam, a huge "Thank You" for you help, that worked brilliantly.

Adam Schwanz @Reply

4 years ago

Additionally, we can add even more to that and make it an after-update event to change the control source of the RunningBalance field so you don't need an additional field. Add this to both of your startdate and enddate afterupdate events

If StartDate<>"" and EndDate <>"" Then

  RunningBalance.ControlSource = "DSum("Credit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)-DSum("Debit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)"

End If

	Adam Schwanz @Reply 4 years ago
Glad to hear ;)

	Paul Maguire OP @Reply 4 years ago
BRILLIANT.......worked a treat......many thanks for all your help it is much appreciated.

Adam Schwanz @Reply

4 years ago

Take out those extra quotations at the start of the dsum and the end, sometime Rick is going to get us an edit button ;)

If StartDate<>"" and EndDate <>"" Then

  RunningBalance.ControlSource = DSum("Credit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)-DSum("Debit","CheckRegT","ID<=" & [ID] & " and ChkDate>=" & [StartDate] & " and ChkDate<=" & [EndDate] & " and AccountID=" & AccountCombo)

End If

	Richard Rost @Reply 4 years ago
Adam, I'm surprised that's working without # around your dates.

	Adam Schwanz @Reply 4 years ago
I was too, was even more surprised that it DIDN'T work WITH them when I tried it once. I took them out and it worked for me. shrug one of those weird things I guess, probably won't work at all if I try it again LOL.

	Richard Rost @Reply 4 years ago
Unless Paul has those as text values in his table...???

Paul Maguire OP @Reply

4 years ago

Hi Richard & Adam. Apologies for not getting back earlier, I was tied up on another project. When I responded 3 days ago that it worked a treat, I was referring to the first piece of code Adam sent me. The later pieces which included the dates did not work. However I did manage to work the first piece of code in the AfterUpdate on StartDate, EndDate and AccFilterCombo, as follows and it works well for me.

Private Sub AccFilterCombo_AfterUpdate()

'AccFilterCombo allows me filter my accounts. Where AccFilterCombo = 1, it returns all my transactions

'for all my accounts. Otherwise it returns transactions for the specific account selected

If Me.AccFilterCombo > 1 And IsNull(Me.StartDate) And IsNull(Me.EndDate) Then

Me.RecordSource = "SELECT qryTransReg.*, qryTransReg.AccountID " & _

" FROM qryTransReg " & _

" WHERE ((qryTransReg.AccountID)=[Forms]![frmTransactionRegister]![AccFilterCombo]);"



ElseIf Me.AccFilterCombo > 1 And (Me.StartDate) <> "" And (Me.EndDate) <> "" Then

Me.RecordSource

Paul Maguire OP @Reply

4 years ago

ElseIf Me.AccFilterCombo > 1 And (Me.StartDate) <> "" And (Me.EndDate) <> "" Then

Me.RecordSource = "SELECT qryTransReg.*, qryTransReg.AccountID " & _

" FROM qryTransReg " & _

" WHERE (((qryTransReg.AccountID)=[Forms]![frmTransactionRegister]![AccFilterCombo]) " & _

" AND ((qryTransReg.TransactionDate)>=[Forms]![frmTransactionRegister]![StartDate] " & _

" AND (qryTransReg.TransactionDate)<=[Forms]![frmTransactionRegister]![EndDate]));"



ElseIf Me.AccFilterCombo = 1 And IsNull(Me.StartDate) And IsNull(Me.EndDate) Then

Me.RecordSource = "SELECT qryTransReg.*, qryTransReg.AccountID " & _

" FROM qryTransReg;"

	Paul Maguire OP @Reply 4 years ago
`ElseIf Me.AccFilterCombo.Value = 1 And (Me.StartDate) <> "" And (Me.EndDate) <> "" Then Me.RecordSource = "SELECT qryTransReg.*, qryTransReg.AccountID " & _ " FROM qryTransReg " & _ " WHERE (((qryTransReg.TransactionDate)>=[Forms]![frmTransactionRegister]![StartDate] " & _ " AND (qryTransReg.TransactionDate)<=[Forms]![frmTransactionRegister]![EndDate]));" End If Me.Requery End Sub`

	Adam Schwanz @Reply 4 years ago
Strange, yea it worked for me when I tried it the first time. Glad you got it working now though, looks good.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View