Solved

Criteria last 2 days excluding weekends

Posted on 2010-08-16
13
406 Views
Last Modified: 2012-05-10
Hi,

how can i show only records where [DateEntered] is less than 2 days but excluding weekends?
So today (Monday), will only show records for Thurs and Fri.
0
Comment
Question by:repco
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 4

Accepted Solution

by:
javaftper earned 500 total points
ID: 33447228

In Access SQL query as long as DateEntered is a date/time field (i.e. not a text field), you can use Format() to test whether the system date (Date() function) returns a Monday or not. If it does, -3 days, otherwise -1 day from Date().  So use a HAVING clause in your SELECT, as follows-

HAVING ((QUERY_TEMP.DateEntered)=IIf(Format(Date(),"ddd")="Mon",Date()-3,Date()-1))

0
 
LVL 18

Expert Comment

by:p912s
ID: 33448255
The following SQL will return the previous two days based on the day of the week; Monday = 2.

SELECT table2.mydate
FROM table2
WHERE (((table2.mydate) Between Date()-4 And Date()-3) AND ((Weekday(Date()))=2)) OR (((table2.mydate) Between Date()-2 And Date()-1) AND ((Weekday(Date()))>2 And (Weekday(Date()))<7));

Replace table2 and mydate with your table and date field names.

Hope that helps.
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33452515
You might find this helpful.

http://support.microsoft.com/kb/210604/

Cheers
JC
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 33453436
Use the function below like this:

  Where [DateEntered] Between ISO_WorkdayAdd(Date(), -1) And ISO_WorkdayAdd(Date(), -2)

/gustav
Public Function ISO_WorkdayAdd( _

  ByVal datDate As Date, _

  ByVal lngWorkdaysAdd As Long, _

  Optional ByVal bytWorkdaysOfWeek As Byte = 5) _

  As Date



' Purpose: Add number of working days to date datDate.

' Assumes: 1 to 7 working days per week.

'          First workday is Monday.

'          Weekend is up to and including Sunday.

' Limitation: Does not count for public holidays.

' May be freely used and distributed.

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen

' 2000-10-03. Constants added.

'             Option for 5 or 6 working days per week added.

' 2002-01-10. Option for 1 to 7 working days per week added.

'             Allowed to add negative number of working days.

'             Adding of zero working days returns the next

'             working day if current day is not a working day.

  

' Test:

' For j = 0 To 10 : For i = 0 to 12 : ? j, i, ISO_WorkdayAdd(Date + j, i): Next i: Next j

 

  ' Minimum and maximum count of workdays per week.

  Const cbytWorkdaysCountMin  As Byte = 1

  Const cbytWorkdaysCountMax  As Byte = 7



  Dim bytMonday               As Byte

  Dim bytSunday               As Byte

  Dim intWeekdayFirst         As Integer

  Dim intWorkdayLast          As Integer

  Dim intDaysShift            As Integer

  Dim lngDays                 As Long

  Dim lngWeeks                As Long

  

  On Error GoTo Err_ISO_WorkdayAdd

  

  If bytWorkdaysOfWeek >= cbytWorkdaysCountMin And bytWorkdaysOfWeek <= cbytWorkdaysCountMax Then

    ' Find ISO weekday for Monday.

    bytMonday = WeekDay(vbMonday, vbMonday)

    ' Find ISO weekday for Sunday.

    bytSunday = WeekDay(vbSunday, vbMonday)

    ' Find ISO weekday for last workday.

    intWorkdayLast = bytMonday + bytWorkdaysOfWeek - 1

    

    ' Find ISO weekday for date datDate.

    intWeekdayFirst = WeekDay(datDate, vbMonday)

    ' Shift date datDate from weekend to Monday.

    If intWeekdayFirst > intWorkdayLast Then

      If lngWorkdaysAdd >= 0 Then

        datDate = DateAdd("d", bytSunday - intWeekdayFirst + 1, datDate)

      Else

        datDate = DateAdd("d", intWorkdayLast - intWeekdayFirst, datDate)

      End If

      ' Find ISO weekday for shifted date datDate.

      intWeekdayFirst = WeekDay(datDate, vbMonday)

    End If

    

    ' Calculate number of days date datDate shall be shifted.

    If lngWorkdaysAdd >= 0 Then

      ' Shift to proceeding Monday in current week.

      intDaysShift = intWeekdayFirst - bytMonday

    Else

      ' Shift to succeeding last workday in current week.

      intDaysShift = intWeekdayFirst - intWorkdayLast

    End If

    ' Shift date datDate.

    datDate = DateAdd("d", -intDaysShift, datDate)

    ' Calculate workdays to add from start/end of current work week.

    lngWorkdaysAdd = lngWorkdaysAdd + intDaysShift

    

    ' Calculate number of workweeks and additional workdays to add.

    lngWeeks = lngWorkdaysAdd \ bytWorkdaysOfWeek

    lngDays = lngWorkdaysAdd Mod bytWorkdaysOfWeek

    

    ' Add number of calendar weeks and additional calendar days to

    ' shifted date datDate.

    If lngWeeks <> 0 Then

      datDate = DateAdd("ww", lngWeeks, datDate)

    End If

    If lngDays <> 0 Then

      datDate = DateAdd("d", lngDays, datDate)

    End If

  End If

  

  ISO_WorkdayAdd = datDate

  

Exit_ISO_WorkdayAdd:

  Exit Function

  

Err_ISO_WorkdayAdd:

  ' Date datDate + lngWorkdaysAdd is outside date range of Access.

  ' Return time zero, 00:00:00.

  Resume Exit_ISO_WorkdayAdd



End Function

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33453660
This query will not be updateable because of the subquery.  The subquery identifies the most recent two dates (prior to today's date) in your table which fall on a weekday.  This is then joined to your table on those dates to return all of the records with a [DateEntered] associated with those two dates.

SELECT yourTable.*
FROM yourTable
INNER JOIN
(SELECT TOP 2 yourTable.[DateEntered] from yourTable
WHERE yourTable.[DateEntered]<Date() AND Weekday(yourTable.[DateEntered],2)<6
ORDER BY yourTable.[DateEntered]) as temp
ON yourTable.[DateEntered] = temp.[DateEntered]

The weekday function returns a numeric value for the day of the week.  The second parameter (firstdayofweek) allows you to shift the "first day of the week" to any day, so if you use 2 (vbMonday), then weekdays will be identified as between 1 and 5.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33453725
Oops, forgot to sort the subquery descending, so that the most recent dates would be at the top.  That should read:

SELECT yourTable.*
FROM yourTable
INNER JOIN
(SELECT TOP 2 yourTable.[DateEntered] from yourTable
WHERE yourTable.[DateEntered]<Date() AND Weekday(yourTable.[DateEntered],2)<6
ORDER BY yourTable.[DateEntered] DESC) as temp
ON yourTable.[DateEntered] = temp.[DateEntered]
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33458675
did you actually try the accepted solution?

The Having clause provided by javaftper is only good with an aggregate query, and will only get you one days worth of information, not two.

The option presented by p912s will work if the current day is Monday, Wed, Thur or Fri, but not on Tuesday.

The only two options that will really return all of the data from your table for the most recent two weekdays prior to the current date are the ones presented by cactus_data and myself.
0
 

Author Comment

by:repco
ID: 33458707
what i did was if weekday is MOnday then Date - 2, if Friday then  date. simple logic that i missed out
0
 
LVL 18

Expert Comment

by:p912s
ID: 33458739
fyed - I was just wondering the same thing?

And when I was reviewing came to the same conclusion you did about my code failing on Tuesday...

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 33458771
Oh well - at least my solution works ...

/gustav
0
 

Author Comment

by:repco
ID: 33458811
sorry i was away for 3 days, i give credit to the first person that answered the same day
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 33458962
I think that the idea behind the site is that other people later can search for solutions to similar questions.

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33460313
I'm not worried about the points, just want to make sure you have a workable solution.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now