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

 

[grtammi]

Hi Regulluz,

Have you tried using the DateDiff function yet? It will count the number of days between one day and another one.

Assuming your boss wants to count ALL days between the 2 dates, and not just the working days (and all your variables are declared as Date variables) ...

DaysBetween = DateDiff("d", InvoiceDate, PaidDate)

HTH

Greg

 

[Regulluz]

Thanks Greg, I'll try that now... Appreciate your help!

Reg

 

[wishlish]

I have a followup on this- is there a way to count the number of BUSINESS days between two dates? I know in Excel you can use the Networkdays function- is there a way of duplicating that function in Access?

 

[grtammi]

This code is courtesy of Helen Feddema - I knew I had seen it around somewhere!

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

'Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, &quot;ddd&quot <> &quot;Sun&quot; And _
Format(DateCnt, &quot;ddd&quot <> &quot;Sat&quot; Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd(&quot;d&quot;, 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

End Function

Call this function using the following syntax:

Workdays InvoiceDate, DueDate

... or whatever your variables are named.

Wishlish: I noticed you had mentioned the Networkdays function - you could use this, at your own risk ... using this Excel function is Access is very risky and not recommended - not very stable ...

HTH

Greg

 

[wishlish]

I think that code is better than using Excel. Thanks, Greg!

 

[Regulluz]

Hello Again!

Guess what? I just can't find where to put the code. I'm not using a form, by the way. So I'm trying to use the DateDiff as an expression in the Query, but it doesn't come out. I'm not that good at Access and the Access Visual Quick Start Guide is not helping me a lot. This is what I'm doing: I created a new field on the payments table named DaysToPay. Then I created a Query out of that table showing only the InvoiceDate, PaymentDate and DaysToPay fields. I expect The calculations to be placed in the empty DaysToPay field. Then, what I do, is that I write the code on the CRITERIA of the DaysToPay

DateDiff(&quot;d&quot; InvoiceDate, PaymentDate)

Excactly as shown, but nothing happens or an error comes. Then I created a new expression field, &quot;DateDiff&quot;and wrote below

(&quot;d&quot; InvoiceDate, PaymentDate)

Nothing happens, or an error message comes out. It's that the right way to do it, or I'm being not so good at it?

Thanks!

 

[wishlish]

I think you need a comma there.

DateDiff(&quot;d&quot; InvoiceDate, PaymentDate)

should be

DateDiff(&quot;d&quot;, InvoiceDate, PaymentDate)

Try that.

 

[MichaelRed]

faq181-261

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Regulluz]

Thankyou All guys!

I finally &quot;got through&quot; with the DaysElapsed: DateDiff, from John. The code was:

DaysElapsed: DateDiff(&quot;y&quot;, [OrderDate], [ShippedDate])

That worked perfectly. Now, I want it to count the days based on TODAY's date. Yo know, that it will count the dayd based on the current date and will update automatically, so now I'm looking on how to make a table to update the date every day.

THANKS!!!

Reg.... I'm going to put a proverb down here soon!

 

[grtammi]

Psst ... Reg ...

You don't need to make a table ... see the Access help files under the Now() function.

Todays_Date = Format(Now(), &quot;mmm-dd-yyyy&quot)

Greg

 

[MichaelRed]

The &quot;y&quot; parameter for the datediff function returns the &quot;day&quot; of the year. While it appears to work, the 'usual' arg for difference in days is &quot;d&quot;.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Regulluz]

Thank you all... Again... I will never ever stop thanking you guys....

THE NOW() worked perfectly!!!! THANKS GREG.. and for you Greg, and Michael.. I DID used the &quot;d&quot; parameter. Sorry for that misspelling, but John, the guy who told me to do it, he wrote &quot;y&quot; instead of the &quot;d&quot;. But then I used Greg's &quot;d&quot;. Then I found that the DaysBetween function did not worked. That's why the code is exacly the same as Greg, but with John's DaysElapsed fucntion.

And Thanks greg for the table thing, but, I don't know that is because the table is already with a lot of records, but I can't add the NOW() to a NEW TodaysDate field in my Invoices table. It, however worked in a new date table, but I can't make my invoices table to lookup the current date in that other table. And when running a query to retrieve both information, the Wizard says theres's a need for a relationship between the two tables, but how is it goint to be, when the TodaysDate table only shows todays date on it's own, without any relation to any other table in the DB. And then, What would I be doing wrong, that the same thing that works for TDays table, doesn't for Invoices table.

Thanks again.... I will send you a sunny picture of the beach here in PR.... for your enjoyment!

 

[grtammi]

Hey Reg ...

Actually, set a variable to Today's Date, such as follows:

Dim dToday As Date
dToday = Format(Now(), &quot;mmm-dd-yyyy&quot) 'or whatever format you wish to use - see help file on Format

... other code here ...

If you need assistance using the date variable through code using a SQL statement, take a look at faq705-2205

HTH ( and thanks for the star!)

Greg

 

[Regulluz]

Greg... I guess you might be working now, so I want to apologize for bothering you too much.. but.. isn't there an not so complicated way?

I'm not a programmer (although I'm starting to like it) and Bob knowledge (although you said he has a stupid name) is faaaaaaaar beyond mines. So I just have no clue of what to do. I started doing it, but got stuck. Since I don't know what query to use, or how to assing to it the table, etc, etc. I think I will need to get rid of a bunch of Queries I have...(21) that I created but are confusing me because of the names I wrote on them.

Anyways... I think I will need to buy a good book.... a very very good one. That way I won't be bothering too much. But the book must teach me at least 1/4th of what you guys know. Any recommendation?

THANKS AGAIN!!

 

[grtammi]

To answer one of your problems: always, ALWAYS name database object with names that will cue you to what they are about ...

ie naming a query as qryCustomers rather than qryInfo (I use naming conventions, and I think a lot of people would recommend that you do too) would give you a clue as to what that query is about.

Now, what I would suggest to you is to take Bob's knowledge, mix it with a little of what you've learned here, add a dash of trial and error experience, and cook at 450 for 1-2 hours ... Such as follows ...

Did you know that you can view the SQL statement behind any query? Go into query design mode, and change the view to SQL view - there's your statement. Now you can copy / paste that into a code module.

Now, here's how you would set the recordsource of a report to this SQL statement ...

In the OnOpen event of your report, type the following code:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String 'declare a variable to hold SQL
'now, let us set the value of the SQL statement
strSQL = &quot;<put SQL statement here>&quot;
Reports!<report name>.RecordSource = strSQL
End Sub

Another tip: You need to practice, practice, practice!!!
Reading the help files never hurts either - I think I have the whole Access help file printed by now ... lol ...

Anything else, that's what we're here for!

HTH

Greg

 

[Regulluz]

Hey Greg....

This is what I'm going to do: I'm goint to print all this info, take it home, read it over and over again, check the help file, play with it with a small version of the DB I have at home (the same one on its first stages) and then I'll let you know what happened. This is too much and the food is burning (my brain). So I need to sit down, relax and read... (since now I just got one Cheddar Lover's from Wendy's) to see If everything goes allright. After all it has been quite a good experience (are you a teacher?). Every time I type those codes in the VB window, and then something appears (MAGIC!!!) to complement... I think I'm gointo to get into the programming stuff.....

BILLIONS OF THANKS GREG.... I'll probably ask you or someone here something else tomorrow

Reg......

 

[wishlish]

Good luck, Reg. And the advice from Greg is right on. Speaking as someone who just finished his first significant Access project, it takes time to learn this stuff right. I've bought so many books off of Half.com that my desk is wall-to-wall manuals! You just have to sit down and let it all stew in.

Ray

 

[KornGeek]

Reg, I recommend saving your money. I have yet to see a book with information anywhere near as good as what can be found searching through the archives on this site. If the archives don't have it, ask the question, and somebody usually has the answer.

Mostly, I recommend just playing around in your spare time, trying out new features. Just back up your database at regular intervals in case you break something. I've been averaging about 2-3 new versions of my current database each day. When I blow something up, I just go back a version or two, and I haven't lost much. This approach gives you the freedom to experiment and learn.

Good luck to you.

 

[Regulluz]

Morning Guys....

A brand new day, and a brand new version of my database is on creation. I have to get rid of all of those queries.... but regardless, this forum if like little cyberheaven. And as KornGeek said (korn fan?), there's no book in the world that would give me those so important advises.

So guys... I have no words to express my gratittude. I'm still gonna be asking (ja ja) but If there's something I can do for you let me know. I have no clue of what it would be but I just wanna return the favor. (sounds weird but that's my personality)

THANKS AGAIN!!!

Reg
daurys@hotmail.com, msn.com, yahoo.com