Solved: Help with DSUM with date criteria

[Jacob Hilderbrand]

I want to have a running balance in a form, and I had it working with this:

=DSum("[Amount]","TblInvoice","[TblInvoice]![InvoiceID]<=[FrmInvoiceID]")+ DSum("[GST]","TblInvoice","[TblInvoice]![InvoiceID]<=[FrmInvoiceID]")

So basically I want to sum the Amount and GST fields. I was basing this off of the InvoiceID and it works, but if I add a new record for a previous date, it would not work.

I wanted to change it to refer to the Date field:

=DSum("[Amount]","TblInvoice","[TblInvoice]![Date]<=[Date]")+ DSum("[GST]","TblInvoice","[TblInvoice]![Date]<=[Date]")

But that just showed #Error.

Can this be made to work with the Date field?

Also, the InvoiceID is just an auto number, so I wonder if there is a way to just reset the numbers so they are in proper order based on the date.

Thanks

[geekgirlau]

Have you tried this (not tested):

=DSum("[Amount]","TblInvoice","[TblInvoice]![Date]<=" & Me.[Date])+ _
DSum("[GST]","TblInvoice","[TblInvoice]![Date]<=" & Me.[Date])


By the way Jake, it's usually not a good idea to name a field after a built-in function - name your date field as "dtmInvoice" or "InvDate" - anything other than "Date"!

[Jacob Hilderbrand]

I get a #Name error on that. You're right about the control names, usually I am careful about that.

File attached.

Thanks

[geekgirlau]

Okay, brain is officially off duty! Dates need # :doh:

=IIf(IsNull([txtDate]),0,DSum("[Amount]","TblInvoice","[TblInvoice]![Date]<=#" & [txtDate] & "#")+DSum("[GST]","TblInvoice","[TblInvoice]![Date]<=#" & [txtDate] & "#"))

PS - I tried renaming the Date field control on the subform, but it may not be necessary as long as the # character is in your formula.

[Jacob Hilderbrand]

That seems to be working thanks. I had tried a variation with #, but wasn't checking for a null value.

Thanks :)