Syntax Error with DSum Statement

[RodShinall]

I am trying to build a personal check register application and I need to write a DSum statement with two criteria as the control source of an unbound text box control which give me a running balance and I can't get the syntax correct. TransactionAmount and TransactionNumber are both number fields and Account is short text. I've tried replacing the double quotes with single quotes but still get an invalid syntax error. Any help would be greatly appreciated.

txtBalance.ControlSource = "=DSum("[TransactionAmount]","tblAccountRegister","[TransactionNumber] <= " & txtTransactionNumber.Value & " AND [Account] = '" & mstrAccountNumber & "')"

 

[MajP]

are the Transaction Number and Account Numbers really numbers or are they numbers stored in a text field. If text you need single quotes delimiter around the value.

 

[arnelgp]

try:

txtBalance.ControlSource = "=DSum(""[TransactionAmount]"",""tblAccountRegister"",""[TransactionNumber] <= " & txtTransactionNumber.Value & " AND [Account] = '" & mstrAccountNumber & "'"")"

 

[RodShinall]

are the Transaction Number and Account Numbers really numbers or are they numbers stored in a text field. If text you need single quotes delimiter around the value

 

[RodShinall]

are the Transaction Number and Account Numbers really numbers or are they numbers stored in a text field. If text you need single quotes delimiter around the value.

TransactionNumber is a number stored in a Number field. Account is a text string stored in a text field.

are the Transaction Number and Account Numbers really numbers or are they numbers stored in a text field. If text you need single quotes delimiter around the value.

 

[MajP]

I had to play with it to get it rigth

Public Sub test()
  Dim str As String
  Dim txttransactionnumber As Long
  txttransactionnumber = 1234
  Dim mstrAccountNumber As String
  mstrAccountNumber = "abcd"
  str = "=DSum(""[TransactionAmount]"",""tblAccountRegister"",""[TransactionNumber] <= " & txttransactionnumber & " And [Account] = '" & mstrAccountNumber & "')"
  Debug.Print str
End Sub

=DSum("[TransactionAmount]","tblAccountRegister","[TransactionNumber] <= 1234 And [Account] = 'abcd')

 

[CJ_London]

why are you assigning the controlsource in code? why not just include it in design view

=DSum("[TransactionAmount]","tblAccountRegister","[TransactionNumber] <= " & txtTransactionNumber & " AND [Account] = '" & mstrAccountNumber & "'")

 

[MajP]

That was my next question because doing that in code is kind of a PITA. Also you could simply set the value

 

[RodShinall]

try:

txtBalance.ControlSource = "=DSum(""[TransactionAmount]"",""tblAccountRegister"",""[TransactionNumber] <= " & txtTransactionNumber.Value & " AND [Account] = '" & mstrAccountNumber & "'"")"

Thanks for your reply. Your code does not produce an error but does not give me the running total.

 

[arnelgp]

not give me the running total.

you need to do it in a Query.

 

[RodShinall]

I had to play with it to get it rigth

Public Sub test()
  Dim str As String
  Dim txttransactionnumber As Long
  txttransactionnumber = 1234
  Dim mstrAccountNumber As String
  mstrAccountNumber = "abcd"
  str = "=DSum(""[TransactionAmount]"",""tblAccountRegister"",""[TransactionNumber] <= " & txttransactionnumber & " And [Account] = '" & mstrAccountNumber & "')"
  Debug.Print str
End Sub

=DSum("[TransactionAmount]","tblAccountRegister","[TransactionNumber] <= 1234 And [Account] = 'abcd')

Thanks for your reply. I'll try this.

 

[Pat Hartman]

Running sum is a property of the control on a report and is a significantly more efficient way to produce a running sum than using a query.

PS - Using Me.ControlName gives you intellisense immediately and the Me. tells Access and people that you are referring to a property of the active form or report.
PPS - the .Value property is the default and so can be omitted and most people do for brevity so Me.ControName and Me.ControlName.Value are equivalent.

 

[RodShinall]

you need to do it in a Query.

You're right, I did this with a query and it works perfectly. Thank you.