query to between 2 dates (1 Viewer)

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
Hi,

Pls help.
In query, i want to get list of task that has exceed 15days for completion excluding weekends(sat & sun).
Include Mon-Fri as days counted.

using below select:

SELECT TASK_NO, TASKNAME, ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE, DATEDIFF(dd, ACTUAL_STARTDATE, ACTUAL_COMPLETIONDATE) AS TimeTaken
FROM TBL_STATUS
WHERE DateDiff("d", ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE) >= 15;

getting all records as output and in field TimeTaken getting ' #Func! '


task_no task_name Actual_startDate Actual_CompletionDate TimeTaken
91799 SalesPlanning 8/16/2018 8/28/2018 #Func!
1673806 SalesCapture 8/17/2018 8/29/2018 #Func!
124486 SalesRelease 8/17/2018 8/27/2018 #Func!
 

Ranman256

Well-known member
Local time
Today, 04:42
Joined
Apr 9, 2015
Messages
4,337
put these into a module then call in query :
HowManyWeekDay (startDte, endDte)

Code:
Public Function HowManyWeekDay(FromDate As Date, _
                            ToDate As Date, _
                            Optional ToDateIsIncluded As Boolean = True)
    
    HowManyWeekDay = DateDiff("d", FromDate, ToDate) - _
                    ToDateIsIncluded - _
                    HowManyWD(FromDate, ToDate, vbSunday) - _
                    HowManyWD(FromDate, ToDate, vbSaturday)
End Function

Public Function HowManyWD(FromDate As Date, _
                            ToDate As Date, _
                            WD As Long)
   ' No error handling actually supplied
    HowManyWD = DateDiff("ww", FromDate, ToDate, WD) _
                                 - Int(WD = Weekday(FromDate))
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:42
Joined
Sep 21, 2011
Messages
14,037
Why do you use one format of Datediff for Timetaken (which is incorrect) and then another for the WHERE statement (which is correct)?


Hi,

Pls help.
In query, i want to get list of task that has exceed 15days for completion excluding weekends(sat & sun).
Include Mon-Fri as days counted.

using below select:

SELECT TASK_NO, TASKNAME, ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE, DATEDIFF(dd, ACTUAL_STARTDATE, ACTUAL_COMPLETIONDATE) AS TimeTaken
FROM TBL_STATUS
WHERE DateDiff("d", ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE) >= 15;

getting all records as output and in field TimeTaken getting ' #Func! '


task_no task_name Actual_startDate Actual_CompletionDate TimeTaken
91799 SalesPlanning 8/16/2018 8/28/2018 #Func!
1673806 SalesCapture 8/17/2018 8/29/2018 #Func!
124486 SalesRelease 8/17/2018 8/27/2018 #Func!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:42
Joined
May 7, 2009
Messages
19,169
you can also create a function that loops:

public function fncDateDiff(d1 as date, d2 as date) as integer
dim dt as date
dim cnt as integer
for dt = d1 to d2
if instr("Sat/Sun", Format(dt, "ddd"))=0 then cnt=cnt+1
next
if cnt>0 then cnt=cnt-1
fncDateDiff=cnt
end function

your query:

SELECT TASK_NO, TASKNAME, ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE, fncDATEDIFF(ACTUAL_STARTDATE, ACTUAL_COMPLETIONDATE) AS TimeTaken
FROM TBL_STATUS
WHERE fncDateDiff(ACTUAL_STARTDATE,ACTUAL_COMPLETIONDATE) >= 15;
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
@arnelgp,
Getting error 'Date type mismatch in criteria expression' with the fncDateDiff & select.

Table datatype:
TASK_NO, Short Text
TASKNAME, Short Text
ACTUAL_STARTDATE, Date/Time
ACTUAL_COMPLETIONDATE, Date/Time


ACTUAL_COMPLETIONDATE can be null, the time take is then #Error
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:42
Joined
May 7, 2009
Messages
19,169
public function fncDateDiff(d1 as date, d2 as variant) as integer
dim dt as date
if isnull(d2) then d2=date()
dim cnt as integer
for dt = d1 to d2
if instr("Sat/Sun", Format(dt, "ddd"))=0 then cnt=cnt+1
next
if cnt>0 then cnt=cnt-1
fncDateDiff=cnt
end function
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
@arnelgp,
i've used the last function u sent and still error
'Date type mismatch in criteria expression' with the fncDateDiff & select.

TASK_NO TASK_NAME ACTUAL_STARTDATE ACTUAL_COMPLETIONDATE TimeTaken

getting #Name? #Name? in all the fields

Hv i missed out something?
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
Ive amended as follow:

Public Function fncDateDiff(d1 As Variant, d2 As Variant) As Integer

If IsNull(d1) Then d1 = Date
If IsNull(d2) Then d2 = Date
Dim cnt As Integer
For dt = d1 To d2
If InStr("Sat/Sun", Format(dt, "ddd")) = 0 Then cnt = cnt + 1
Next
If cnt > 0 Then cnt = cnt - 1
fncDateDiff = cnt
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:42
Joined
May 7, 2009
Messages
19,169
i think you are in the making of a good programmer!
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
It's not my field of expertise @ all..
Thxs a lot for ur help :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:42
Joined
May 7, 2009
Messages
19,169
in time you shall reap if you faint not!
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
hi,

Pls help...
getting error
undefined function 'fncDateDiff' in expression
when opening / running the query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:42
Joined
May 7, 2009
Messages
19,169
put it i a Module.
 

akika

Registered User.
Local time
Today, 01:42
Joined
Aug 7, 2018
Messages
102
Ive changed ze function & module name and was ok.
thxs
 

Users who are viewing this thread

Top Bottom