query to between 2 dates

[akika]

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]

put these into a module then call in query :
HowManyWeekDay (startDte, endDte)

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]

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]

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]

@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]

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]

@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]

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]

i think you are in the making of a good programmer!

 

[akika]

It's not my field of expertise @ all..
Thxs a lot for ur help

 

[arnelgp]

in time you shall reap if you faint not!

 

[akika]

hi,

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

 

[arnelgp]

put it i a Module.

 

[akika]

Ive changed ze function & module name and was ok.
thxs