Sort by days out

[dbartelt]

Hello,

I need help sorting by InvoiceDate for the AgedReceivable form.

I have place 4 text fields in the footer of the subfrmStatements forms. The control source for each text field is =IIf(IsNull([InvoiceBalance]),0,Sum([InvoiceBalance])). I need help finishing the code.

The sub form shows only the records related to a specific customer that is selected on the main form. I would like to sort the InvoiceBalance by the InvoiceDate field with the following parameters:


0 – 30 days
31 – 60 days
61 – 90 days
over 91 days


Thanks in advance for any help!

DBartelt

 

[ThreeCrow]

Do this in a saved Query, you can name it qryCalculateCustomerAging.


InvoiceNumber

ShippedDate

0-30 days
Current: IIf(DateDiff("d",[ShippedDate],Date())<=30,([TotalInvoice]-[TotalPaid]),0)

31-60 days
Over30: IIf(DateDiff("d",[ShippedDate],Date()) Between 31 And 60,([TotalInvoice]-[TotalPaid]),0)

61-90
Over60: IIf(DateDiff("d",[ShippedDate],Date()) Between 61 And 90,([TotalInvoice]-[TotalPaid]),0)

Over90
Over90: IIf(DateDiff("d",[ShippedDate],Date())>90,([TotalInvoice]-[TotalPaid]),0)

Balance
Balance: Sum(nz([InvoiceTotal])-nz([TotalPaid]))

For the above Query Fields
ShippedDate=Date of your Invoice

TotalInvoice=The total of your Invoice
Calculate in a seporate Query named qryInvoiceTotal, using your Invoice Detail table as the Rcord Source.

TotalPaid=The Total that the Customer has paid to that Invoice
Calculate in another seporate Query qryInvoicePaidTotal, using your Customer Payment Table as the RecordSource

Use this Query as the Record Source for your subForm.

Doing this with a saved Query, you will be able to use the same Query for other Forms, subForms, or Reports

Hope this helps