×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Determining Invoices 30 days past due

Determining Invoices 30 days past due

Determining Invoices 30 days past due

(OP)
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!

RE: Determining Invoices 30 days past due

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

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


Mike Rohde
rohdem@marshallengines.com

RE: Determining Invoices 30 days past due

(OP)
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'.

RE: Determining Invoices 30 days past due

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


Mike Rohde
rohdem@marshallengines.com

RE: Determining Invoices 30 days past due

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

RE: Determining Invoices 30 days past due

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close