Counting Days in Access - How???? 2

[Regulluz]

Hello All....

After a lot of work in a database for accounts billings, my boss now wants to see how long the customers took to pay. While I was just tracking if they paid or not, now I have to make Access to count the days from the Invoice date up to the date the invoice is paid. I have a customers, invoices, and suppliers tables. In suppliers there's a terms field, which says the terms of payment, but I have not taken advantage of it. Every table is linked by its current ID, where Customers to Invoices by CustID and Suppliers to Invoices by SupplID. InvoiceNumber is the primary key for Invoices, and, then I'm running 20 queries around those tables, and 3 reports of those 20 queries. None of them shows how long it took for customers to pay. Now, the Invoices table does have an Invoice Date and a Due date based in the terms, which I count myself and add the date based in my calculations. The question is, How do I make acces to count the days and make a report with the outcome?

Sorry for the looooooong parragraph. Thanks anticipated.

reg

 

[Regulluz]

I DID IT!!!!!!!!!!

It now counts my Elapsed Days based on todays date!!!! What did I did, well... in an InvoicesNot Paid Query, I added an InvoiceDate Field, and then put the TodaysDate Expression as a new field. It worked. I have now every invoice Date and Today's date working on the same Query... so, what's next, I put in a new field the DaysElapsed expression of Greg and John, and VIOLA!! I've got the DAYS ELAPSED!!!! Now I need then on a report, and that's what I'm going to be working on now...

BILLION THANKS MASTERS!!!!


Reg.

 

[wishlish]

Just a note: While Tek-Tips is great, there are some fantastic books out there for beginning Access developers. I'd recommend:

Access Database Design & Programming by Steven Roman- a very analytical look at database design and how Access handles these concepts. Very high-level, well suited for the mathematically minded.

Running Microsoft Access 97 by Viescas- more of a walk through, and I found the book quite useful to the beginner. Nice start.

Beginning Access 97 VBA by Smith and Sussman- a VBA tutorial. Great stuff here.

And once you've got that under your belt, try
Access Cookbook by Getz, Litwin and Baron and
Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
Access 97 Expert Solutions by Leszynski (great for the 300 page essay on what professional developement entails)
Most of these books are available on half.com since they're the A97 books. If you're using a later iteration, all have been updated.

 

[mikey69]

Litwin, Getz and Gunderloy is the latest version of No 2 above. I think it is the best book of many that I've looked at. It includes a synopsis of the Hungarian convention for naming database objects. I endorse absolutely everything that has been said about naming conventions.