How calculate days difference in weekdays

I have this in a query but want to adjust it to calculate weekdays.  What is the correct syntax for that?

LateDays: DateDiff("d",[DropDueDate],[ReschDropDueDate])

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
DateDiff("w",[DropDueDate],[ReschDropDueDate])
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
more ...
Capture1.gif
Rey Obrero (Capricorn1)Commented:
if by weekdays you mean, excluding weekends, place this codes in a regular module


Function getWeekDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
If vDate1 = vDate2 Then getWeekDays = 0: Exit Function
 
    dtStart = vDate1
    i = DateDiff("d", vDate1, vDate2) + 1
        Do Until dtStart >= vDate2
            dtStart = dtStart + 1
            Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7
                    dtStart = dtStart + 1
                    i = i - 1
            Loop
        Loop

getWeekDays = i
End Function

Open in new window



to use in your query

LateDays: getWeekDays([DropDueDate],[ReschDropDueDate])

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteveL13Author Commented:
Hmmm, when I run it I get a "Data type mismatch in criteria expression.

???
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Steve ... are you trying to account for weekends or not?
mx
SteveL13Author Commented:
I want to EXCLUDE weekends.  So for example, if DropDueDate was 4/21/2011 and ReschDropDueDate was 4/25/2011 the answer should be 2.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Then ignore my first post.  Probably good to mention that in the original Q :-)

mx
Rey Obrero (Capricorn1)Commented:
what Criteria? i don't see any criteria in the expression you posted
SteveL13Author Commented:
I want to EXCLUDE weekends.  So for example, if DropDueDate was 4/21/2011 and ReschDropDueDate was 4/25/2011 the answer should be 2.
Rey Obrero (Capricorn1)Commented:
so, where is the criteria
SteveL13Author Commented:
Here is my SQL...

SELECT tblProjectDetail.DetailID, tblProjectDetail.JobN, tblProjectDetail.Complete, tblProject.CompanyName, tblProject.ProjType, tblProjectDetail.DropDueDate, tblProjectDetail.ReschDropDueDate, getWeekDays([DropDueDate],[ReschDropDueDate]) AS LateDays, tblRescheduleReasons.Reason
FROM (tblProjectDetail LEFT JOIN tblProject ON tblProjectDetail.JobN = tblProject.JobN) LEFT JOIN tblRescheduleReasons ON tblProjectDetail.JobN = tblRescheduleReasons.JobN
WHERE (((tblProjectDetail.Complete)=True) AND ((getWeekDays([DropDueDate],[ReschDropDueDate]))>0));
Rey Obrero (Capricorn1)Commented:
check if you have null values in fields [DropDueDate] and [ReschDropDueDate]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.