Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: Take 3 days off of a date

    Hi All,

    I have two text boxes on a form. I want to add a date into txtBoxA and I need code to automatically make the value in txtBoxB to be 3 days less than txtBoxA.

    The tricky part is that I do not want to count weekends.

    So for example if I have Thursday 11th March as "A","B" should be Monday 8th March.

    Whereas If I Have Weds 10th March as "A", "B" should be Friday 5th March and NOT SUNDAY.

    Is there a way to do this in VBA code?

    Thanks
    Simon

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so implement some code so that when you change the value in one control that fires off a procedure/function to calculate the date.

    use datediff to do the subtraction, and if needs be subtract further days if the derived date is a weekend

    theres is a function called (IIRC) dayofweek
    Date Functions | Database Solutions for Microsoft Access | databasedev.co.uk
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    I have done it this way:

    Private Sub txtDespatchDate_AfterUpdate()
    Dim datDespatchDate As Date
    Dim datOnFabricDate As Date
    Dim strDay As String

    datDespatchDate = txtDespatchDate

    strDay = DatePart("w", datDespatchDate)

    Select Case strDay
    Case 7
    datOnFabricDate = datDespatchDate - 4
    Case 5 To 6
    datOnFabricDate = datDespatchDate - 3
    Case 2 To 4
    datOnFabricDate = datDespatchDate - 5
    Case 1
    datOnFabricDate = datDespatchDate - 4
    End Select

    txtOnFabricDate = datOnFabricDate
    End Sub

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats fine

    but bear in mind it works because of a quirk in JET the underlying storage mechanism. to make your code more robust you are better off using a specific function which will always give the right answer irrespective of how the underlying data is stored. y'never know if or how a vendor may change the underlying data, using the function will mean that should you change to MySQL or something else it will work without further testing and coding.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2009
    Posts
    258
    Quote Originally Posted by healdem View Post
    use datediff to do the subtraction, and if needs be subtract further days if the derived date is a weekend
    I think you mean to use the DateAdd function.

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Here's a bit I snagged somewhere (Thinkin MS site) for my employee attendance db. The way it is here it'll always take the date to the corresponding Monday. Should be something you can get out of here to take you to the correct day if someone selects a weekend date.


    Code:
        Dim tmp As Integer
        tmp = Weekday(CDate(txt_Date))
        
        If tmp <> vbMonday Then
            Select Case tmp
                Case 1
                    txt_Date = dhNextDOW(2, txt_Date)
                Case 3
                    txt_Date = dhPreviousDOW(2, txt_Date)
                Case 4
                    txt_Date = dhPreviousDOW(2, txt_Date)
                Case 5
                    txt_Date = dhPreviousDOW(2, txt_Date)
                Case 6
                    txt_Date = dhPreviousDOW(2, txt_Date)
                Case 7
                    txt_Date = dhPreviousDOW(2, txt_Date)
            End Select
        End If
    
    End Sub
    
    Function dhPreviousDOW(intDOW As Integer, _
     Optional dtmDate As Date = 0) As Date
     
        ' Find the previous specified day of the week
        ' before the specified date.
        Dim intTemp As Integer
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        
        intTemp = Weekday(dtmDate)
        dhPreviousDOW = dtmDate - intTemp + intDOW - _
         IIf(intTemp > intDOW, 0, 7)
    
    End Function
    
    Function dhNextDOW(intDOW As Integer, _
     Optional dtmDate As Date = 0) As Date
        
        ' Find the next specified day of the week
        ' after the specified date.
        Dim intTemp As Integer
        
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        
        intTemp = Weekday(dtmDate)
        dhNextDOW = dtmDate - intTemp + intDOW + _
         IIf(intTemp < intDOW, 0, 7)
    
    End Function
    Sam, hth
    Good, fast, cheap...Pick 2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •