How to do a simple countdown for date?

[xaxier]

Hi all,

I need to do a simple countdown for number of days left from the due date for a task. Anyone know how this can be achieved using query?

Thanks

 

[DCrake]

If you know the cut off date and want to compare it to today then you can use the DateDiff() function

 

[xaxier]

Hi DCrake,

How to use this function and always compare it against today's date? For example,

Day_Due: DateDiff("d",[Date],[Today_Date])

So what should I put in the column for Today_date? In Excel, we can use simple formula such as =now() for today's date but what about Access?

Thanks in advance.

 

[raskew]

Hi --

Now() works in Access. if you need to include both date and time.

Date() returns only the current date (no time).

Example from the debug window:

? date()
3/6/2011

? now()
3/6/2011 12:13:46 AM

HTH - Bob

 

[xaxier]

Thanks a lot, seem to be working.

How about if I want it to countdown based on working days only? ( Mon-Fri). In excel, we have something like Networkdays( a,b), how about Access?

 

[raskew]

Hi again -

Try copying this to a module, then test. The example shown is
using Date() = 3/6/11

'___________________________________________________________

Function DateDiffExclude3(pstartdte As Date, _
penddte As Date, _
pexclude As String) As Integer
'*********************************************
'Purpose: Return number of days between two
' dates, excluding weekdays(Sun = 1
' thru Sat = 7) specified by user
'Coded by: raskew
'Inputs: from debug (immediate) window:
' -excludes Saturday (7) and Sunday (1) from count
' ? DateDiffExclude3(date(), #7/13/11#, "17")
'Output: 93
'*********************************************

Dim WeekHold As String
Dim WeekKeep As String
Dim FullWeek As Integer
Dim OddDays As Integer
Dim n As Integer

WeekHold = "1234567123456"
'get # of full weeks (7 days) & convert to # of days
FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
'get # of days remaining after FullWeek is determined
OddDays = (penddte - pstartdte + 1) Mod 7
'string representation of the weekdays contained in OddDays
WeekKeep = Mid(WeekHold, WeekDay(pstartdte), OddDays)
'use boolean statement to reduce OddDays by 1 for each
'pexclude weekday found in WeekKeep
For n = 1 To Len(pexclude)
OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
Next n

DateDiffExclude3 = FullWeek + OddDays

End Function
'___________________________________________________________

HTH - Bob

 

[xaxier]

wow, thanks a lot for the sample code. I have pasted it in the module,seems not working? Any idea? I am a noob when it come to programing, so pardon me if my question sound dumb

 

[xaxier]

Hi raskew,

Do you have any idea why my form still unable to calculate by business day? Do I miss out any steps?

 

[raskew]

Hi -

What is it doing or not doing? I just tested it, copying the code from post #6, and it works as advertised.

Try this from the debug window:

? DateDiffExclude3(date(), #7/13/11#, "17")

When I test it, it returns 91 (today being 3/9/11).

Please provide more info, including name of your module (must not be the same as the function).


Bob

 

[xaxier]

Hi raskew,

Thanks for the effort in answering my question. I am a total noob when come to programing stuff.

My module name is "Networkdays"> anyway, I believe it is a problem with the formula I am using. I am using Expr1: DateDiffExclude3(Date(),[Date_Start],"17") but it seem run into error. If I use DateDiffExclude3(date(), #7/13/11#, "17"), it return 90 days as well, so I don't think it is the module error, more to the way how I execute it

I am not too sure how to execute the formula, can you teach me how?

For Example: the countdown is based on the information in Date_Start column, what should I put in?

Thanks a lot and sorry if the question sound noob

 

[raskew]

Hi -

It would certainly sound like your [Date_Start] field is the problem. What is its' data type and format?

Bob

 

[xaxier]

Hi raskew,

Data type for Date_start is "Date/Time", I did not specify any format for it.

Thanks!

 

[raskew]

Okay. Any chance you can download sample data?

Bob

 

[xaxier]

Hi raskew,

I am not sure what you mean? You mean I need to download the sample data? If Yes, from where?

Thanks