payables aging text box on form

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a table that shows union dues for various memebers. They are billed
monthly and I need to show four text boxes on a form that displays a list of
transactions for a member in a sub form. The text boxes need to show current
payable, 30-60 days, 60-90 days and over 90

Know I can use a query using the "between" in the criteria but not clear as
to how to get the information into the text boxes. Is it best to run a
recordset at form load using the subforms recordsource as the source?

fields are MemberID, DuesID, Debit, TransDate, Allocated. If Allocated
checked then that due has been paid.

Tried using a crosstab query but seem to get hung up with column heandings
and row headings when using sum as a value on the Debit field.

Hope someone can point me in the right direction.

Thanks
Chris
 
The easiest way to tackle this is to base your form in a query with
calculated fields like this:

Current:iif(not([Allocated],iif(Date()-[Transdate] < 30,[Debit],0),0)
30-60 Days: iif(not([Allocated],iif(Date()-[Transdate] between 30 and
60,[Debit],0),0)
61-90 Days: iif(not([Allocated],iif(Date()-[Transdate] between 61 and
90,[Debit],0),0)
Over 90:iif(not([Allocated],iif(Date()-[Transdate] > 90,[Debit],0),0)
 
Back
Top