Date Calculation

[Nikkidee]

I am rusty in expression building. I am creating a dateadd weekday in expression builder, I need create a Friday date prior to a [duedate]. This is the expression I currently have,
Mailing Date: DateAdd("d",-7-Weekday(Date()),[Date Due])

It is not creating a Friday date. Please help

 

[boblarson]

Try this:

This code was generously donated by Bob Askew (raskew) from Access World Forums and modified by Bob Larson to be able to look either forward or back.
[B][COLOR=#ff0000]NOTE:  If you use this in a QUERY or Control Source, you have to specify the day NUMBER instead[/COLOR][/B]
[B][COLOR=#ff0000]of vbWednesday, vbSunday, etc. because queries and control sources do not know about the VBA [/COLOR][/B]
[B][COLOR=#ff0000]constants.[/COLOR][/B]
 
Function fNextNthDay(dteStart As Date, _
                     intWeekday As Integer, Optional blnPrevious As Boolean) As Date
'************************************************* *
'Purpose: Round date up to next specified
' weekday
' The optional parameter blnPrevious specifies if you want
' the PREVIOUS date.  The default is to get the NEXT date.
 
 
'Inputs:
' 1) ? fNextNthDay(#4/18/06#, vbWednesday)  
' 2) ? fNextNthDay(#4/19/06#, vbWednesday)
' 3) ? fNextNthDay(#4/20/06#, vbWednesday)
' 4) ? fNextNthDay(#4/19/06#, vbWednesday, True)
' 5) ? fNextNthDay(#4/20/06#, vbWednesday, True)
'Output:
' 1) 4/19/06
' 2) 4/19/06
' 3) 4/26/06
' 4) 4/19/06
' 5) 4/19/06
'************************************************* *
    If blnPrevious Then
        fNextNthDay = (dteStart - Weekday(dteStart) + _
                       intWeekday + _
                       IIf(Weekday(dteStart) < intWeekday, -7, 0))
    Else
        fNextNthDay = dteStart - Weekday(dteStart) + _
                      intWeekday + _
                      IIf(Weekday(dteStart) > intWeekday, 7, 0)
    End If
End Function

And then you can call it like this in the query

Mailing Date: fNextNthDay([Date Due], 6, True)

 

[Nikkidee]

Thanks, It works. But lets say I if a due date lands on a friday it needs to have the Mailed Date the Friday prior to that, I know it is an 'If".

 

[boblarson]

Thanks, It works. But lets say I if a due date lands on a friday it needs to have the Mailed Date the Friday prior to that, I know it is an 'If".

Add this to the fNextNthDay function (just before the End Function part):

    If Weekday(fNextNthDay) = 6 Then
        fNextNthDay = DateAdd("d", -7, fNextNthDay)
    End If

 

[Stemdriller]

Hi Bob

I have a similar issue but I just need text2 to feed of Text1

eg If Text 1 is 12/07/2011 Text 2 should 33 days before, but need to omit weekends and holidays.

I've searched through the forum and tried various suggestions but none have worked so far, (probably my lack of vba understanding)

What would you suggest?

Thanks
Gareth

 

[raskew]

Hi -

See my post at this thread.

HTH - Bob

 

[boblarson]

Hi Bob

I have a similar issue but I just need text2 to feed of Text1

eg If Text 1 is 12/07/2011 Text 2 should 33 days before, but need to omit weekends and holidays.

I've searched through the forum and tried various suggestions but none have worked so far, (probably my lack of vba understanding)

What would you suggest?

Thanks
Gareth

See if this will help:
http://www.utteraccess.com/forum/Add-subtract-number-bu-t95181.html