Only weekdays in datediff

  • Thread starter Thread starter Y Beckers via AccessMonster.com
  • Start date Start date
Y

Y Beckers via AccessMonster.com

I have this Query,

SELECT DateDiff('w',[dat1],[dat2]) AS Expr1, *
FROM Dat
WHERE DateDiff('w',[dat1],[dat2]);


Now i want to count only the weekdays, so exclude the weekenddays. How do I do this. Please give a clear answer, cause i'm new on Access
 
Check the Date/Time section of "The Access Web". There are a couple of
solutions there.

http://www.mvps.org/access/

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Y Beckers via AccessMonster.com said:
I have this Query,

SELECT DateDiff('w',[dat1],[dat2]) AS Expr1, *
FROM Dat
WHERE DateDiff('w',[dat1],[dat2]);


Now i want to count only the weekdays, so exclude the weekenddays. How do
Click to expand...
I do this. Please give a clear answer, cause i'm new on Access
 
<<I have seen these, but I don't understand them.???>>

Yeah, me neither.
I have been using Access for several years and unfortunately I am still not
adept at
using VBA. I took another approach to calculating weekdays that
unfortunately is
somewhat flawed but for my purposes was adequate. Perhaps it will be useful
to you.
In a query based on my Orders table I included Order#, OrderDate, and
DateRcvd fields.
In an open column of the QBE grid I entered this:
Days: DateDiff("d",[OrderDate],[DateRcvd])

Then in another column, I entered this:
Weekend: DateDiff("ww",[OrderDate],[DateRcvd])*2

And in a third column I entered this:
Weekday: [Days]-[Weekend]

Now, admittedly this is pretty crappy, but when you are calculating
leadtimes as I was, you realise that in the
real world, shipping times vary anyway. So, crappy was better than nothing.
At least, for my purposes. And since no
one else seems inclined to respond to your post after all these hours, I
figure my response is probably better than no response.

Good luck.

Ed G
 
Much appreciated. Initially, I got an invalid use of null message when I
tried adding
this function to a query, but adding "is not null" to the criteria for
[DateRcvd] cured that
Looking over the returned results, I would say that Microsofts Weekday
function is almost
as crummy as mine. For instance, if you order something on Saturday and it
arrives Monday,
this function returns zero too. Where mine really fails though is if you
order something on a Sunday
and it arrives on a Thursday or if you order on a Wednesday and it arrives
Saturday. This was not
a problem for me though, because we were closed on the weekend. :-)

Ed
 
Back
Top