Determining Invoices 30 days past due

[djgolds]

I have a form for my customers (created from a table) and I want to pull information from my Invoices table to sum unpaid invoices that are 30 days or older for the specific record (customer) displayed on the screen. My columns in the invoices table are:

Invoice, Invoice Date, Amount, Date Paid.

In an unbound text box for the properties control source I've entered so far:

=DSum("[Amount]","[Invoices]",[CompanyID]=[txtCompanyID]

What should the remaining criteria be to find the records in the Date Paid column that are null (no date entered yet as they aren't paid) and to total all unpaid invoices that are 30 or 30+ days old? Thanks!

 

[Rohdem]

I believe this should work (but I didn't test it)

=dsum(&quot;[amount]&quot;, &quot;Invoices&quot;, &quot;[CompanyID] = '&quot; &amp; txtcompanyID &amp; &quot;' AND Is Null([date paid])AND [INVOICE DATE] <= &quot; &amp; (date() - 30))

Mike Rohde
rohdem@marshallengines.com

 

[djgolds]

Mike - I tried it and I got this message. Please let me know if you have additional suggestions. Thanks!

Syntax error (missing operator) in query expression '[CompanyID]='18' AND Is Null([datepaid])AND [InvoiceDate],=11/21/00'.

 

[Rohdem]

I knew that didn't look quite right!!! Date's need the '#' sign around them. Try..
=dsum(&quot;[amount]&quot;, &quot;Invoices&quot;, &quot;[CompanyID] = '&quot; &amp; txtcompanyID &amp; &quot;' AND Is Null([date paid]) AND [INVOICE DATE] <= #&quot; &amp; (date() - 30) &amp; &quot;#&quot

Mike Rohde
rohdem@marshallengines.com

 

[Guest_imported]

=dsum(&quot;[amount]&quot;, &quot;Invoices&quot;, &quot;[CompanyID] = &quot; &amp; txtcompanyID &amp; AND Is Null([date paid])AND [INVOICE DATE] <= #&quot; &amp; (date() - 30)) &amp;&quot;#&quot;

 

[MichaelRed]

My experience is that even when you get the domain aggregate function set up properly, you won't like the results. It will take a lot longer than you think do get the results, because the domain aggregate functions take a lot of time to 'execute'. You can almost always get better performance from a query set up return the same recordset, with the advantage that you can get a SET of results as opposed to the single value response.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over