• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Find nearest Thursday In access

I have a field that has a enter entered in and I need some code that will look at that date and find the nearest thursday and also tell if its a holiday. I already have a holiday table "tblholiday"



i'm clueless how to do this.

0
Cyprexx IT
Asked:
Cyprexx IT
  • 4
  • 2
2 Solutions
 
pritamduttCommented:
Hi,

I am giving you an MS Access SQL Query to achieve the same.

select IIF(WeekDay(NOW())<5,dateadd("d",5-WeekDay(NOW()),now()),dateadd("d",7-(WeekDay(Now())-5),Now()))

Open in new window


For sample I have used today's date.

Please replace NOW() with input date!


Hope this helps!
Regards,
0
 
pritamduttCommented:
Sample Runs
select IIF(WeekDay(#01-Nov-2011#)<5,dateadd("d",5-WeekDay(#01-Nov-2011#),#01-Nov-2011#),dateadd("d",7-(WeekDay(#01-Nov-2011#)-5),#01-Nov-2011#))
select IIF(WeekDay(#02-Nov-2011#)<5,dateadd("d",5-WeekDay(#02-Nov-2011#),#02-Nov-2011#),dateadd("d",7-(WeekDay(#02-Nov-2011#)-5),#02-Nov-2011#))

Open in new window


Result: 03-Nov-2011

select IIF(WeekDay(#03-Nov-2011#)<5,dateadd("d",5-WeekDay(#03-Nov-2011#),#03-Nov-2011#),dateadd("d",7-(WeekDay(#03-Nov-2011#)-5),#03-Nov-2011#))
select IIF(WeekDay(#04-Nov-2011#)<5,dateadd("d",5-WeekDay(#04-Nov-2011#),#04-Nov-2011#),dateadd("d",7-(WeekDay(#04-Nov-2011#)-5),#04-Nov-2011#))
select IIF(WeekDay(#05-Nov-2011#)<5,dateadd("d",5-WeekDay(#05-Nov-2011#),#05-Nov-2011#),dateadd("d",7-(WeekDay(#05-Nov-2011#)-5),#05-Nov-2011#))
select IIF(WeekDay(#06-Nov-2011#)<5,dateadd("d",5-WeekDay(#06-Nov-2011#),#06-Nov-2011#),dateadd("d",7-(WeekDay(#06-Nov-2011#)-5),#06-Nov-2011#))
select IIF(WeekDay(#07-Nov-2011#)<5,dateadd("d",5-WeekDay(#07-Nov-2011#),#07-Nov-2011#),dateadd("d",7-(WeekDay(#07-Nov-2011#)-5),#07-Nov-2011#))
select IIF(WeekDay(#08-Nov-2011#)<5,dateadd("d",5-WeekDay(#08-Nov-2011#),#08-Nov-2011#),dateadd("d",7-(WeekDay(#08-Nov-2011#)-5),#08-Nov-2011#))
select IIF(WeekDay(#09-Nov-2011#)<5,dateadd("d",5-WeekDay(#09-Nov-2011#),#09-Nov-2011#),dateadd("d",7-(WeekDay(#09-Nov-2011#)-5),#09-Nov-2011#))

Open in new window

Result: 10-Nov-2011

select IIF(WeekDay(#10-Nov-2011#)<5,dateadd("d",5-WeekDay(#10-Nov-2011#),#10-Nov-2011#),dateadd("d",7-(WeekDay(#10-Nov-2011#)-5),#10-Nov-2011#))

Open in new window

Result: 17-Nov-2011
0
 
Cyprexx ITAuthor Commented:
This is going to sound dumb, I don't even know how to insert a SQL statement for a field
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
pritamduttCommented:
can u share the sample mdb file?
0
 
mbizupCommented:
I think using VBA might be a little more straight-forward.

This will do the trick for the nearest thursday:

Function GetNearestThursday(dt As Date) As Date
    Dim intWeekDay As Integer
    Dim dtTemp As Date
    Select Case Weekday(dt)
        Case 5 ' GetNearestThursday is day 5, so nearest Thursday is the date passed to the function
            dtTemp = dt
        Case Is < 5 ' Date calculation for Sunday or Monday
            dtTemp = DateAdd("d", 5 - Weekday(dt), dt)
        Case Is > 5 ' Date calculation for other weekdays
            dtTemp = DateAdd("d", 12 - Weekday(dt), dt)
    End Select
    
    'If the calculated nearest thursday is in the holiday table, add 7 days
    ' **** Note -- You need to adjust the following statement to use the correct table and field names ***
    If DCount("*", "YourTable", "Format([YourDateField],'yyyymmdd') = '" & Format(dtTemp, "yyyymmdd") & "'") > 0 Then
       GetNearestThursday = DateAdd("d", 7, dtTemp)
    Else  ' Otherwise simply return the calculated date
       GetNearestThursday = dtTemp
    End If
End Function

Open in new window


Call it like this:

msgbox(GetNearestThursday(date()))
0
 
mbizupCommented:
This is a more generic version that should work for any weekday:

Function GetNearestWeekday(intWD As Integer, dt As Date) As Date
    Dim intWeekDay As Integer
    Dim dtTemp As Date
    If intWD < 1 Or intWD > 7 Then
        MsgBox "The weekday you have entered is out of range.  Please use 1-7 as follows:" & vbCrLf & _
            "1: Sunday" & vbCrLf & _
            "2: Monday" & vbCrLf & _
            "3: Tuesday" & vbCrLf & _
            "4: Wednesday" & vbCrLf & _
            "5: Thursday" & vbCrLf & _
            "6: Friday" & vbCrLf & _
            "7: Saturday"
        Exit Function
    End If

    Select Case Weekday(dt)
        Case intWD ' Checks if date passed falls on the desired weekday.  If so nearest weekday is the date passed to the function
            dtTemp = dt
        Case Is < intWD ' Date calculation for days prior to the one passed
            dtTemp = DateAdd("d", intWD - Weekday(dt), dt)
        Case Is > intWD ' Date calculation for other weekdays
            dtTemp = DateAdd("d", intWD + 7 - Weekday(dt), dt)
    End Select
    
    'If the calculated nearest thursday is in the holiday table, add 7 days
    ' **** Note -- You need to adjust the following statement to use the correct table and field names ***
    If DCount("*", "YourTable", "Format([YourDateField],'yyyymmdd') = '" & Format(dtTemp, "yyyymmdd") & "'") > 0 Then
       GetNearestWeekday = DateAdd("d", 7, dtTemp)
    Else  ' Otherwise simply return the calculated date
       GetNearestWeekday = dtTemp
    End If
End Function

Open in new window


You'd call it by passing a date to the function and an integer weekday, ie: 1 if you want the nearest Sunday, 2 for Monday, 3 for Tuesday, etc like this:

msgbox(GetNearestWeekday(1, date()))    'Nearest Sunday
msgbox(GetNearestWeekday(2, date()))    'Nearest Monday
msgbox(GetNearestWeekday(6, date()))    'Nearest Friday

'etc

Open in new window


0
 
pritamduttCommented:
Hi!

I have created a sample Access database to demonstrate how Next Thursday can be calculated based on date input into one of the fields.

Please find attached sample database.

Logic Used:

On the exit event of the input field, Next Working Thursday is calculated. You will be notified if the Thursday is holiday, and will be given a choice to find next working thursday.

Hope this helps!

Regards,
sample.mdb
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now