Amt Due on old transaction

[davidbodhi]

I'm not sure what to search for to figure this one out. My searches, so far, haven't helped me much.

I'm tracking transactions in a clinic. The amount due for the visit is calculated fine, but sometimes people don't pay right away. (And sometimes pay ahead, which will just be a negative amount due.) I need my transaction to not only show what's due from today's visit, but also to look at previous transactions of the same client and calculate what may still be due from before. Additionally, if I pull up an OLD record, I need it to show what WAS due on that date, not what's due today.

So, I know I need to refer to dates prior to the date on the form, regardless whether it's today's date (for a new visit) or a previous date (for a previous visit). That's what I'm confused about.

How do I refer to [txtDate] on THIS form and not confuse Access with [txtDate] on earlier forms?
Code like this:
SUM[blahblahblah] WHERE [txtDate] < [txtDate] surely won't work.
Does:
SUM[blahblahblah] WHERE [txtDate] < [Me].[txtDate] work?

Should this kind of calculation be in a query or in the Control Source itself, or somewhere else?

And do I need the square brackets or not?
Do I use '.' or '!'?

 

[Big2]

Could just use it on the form itself. Then txtDate is txtDate on the current form and not somewhere else. If your referencing to another form you would need Forms!FORMNAME!txtDate.

 

[davidbodhi]

Well, Big.. this is what's confusing to me, or part ofit. I'll be referring to earlier instances of the same form: frmTransactions.

Today's frmTransactions needs to look at previous frmTransactions. I need to know how to distinguish between fields of the same name on THIS and PREVIOUS frmTransactions.

Is what you suggested adequate to do so?