Query Date Round Up To Particular Day

[jereece]

I track reports in a database. I have a date field [Approval_Date] in the database table. I want to review the reports after they have been approved for 6 months to see if the actions fixed the problem, so I created a query based on the table and used the code "6-Month Review Date: [Approval_Date]+180". This works great except that our meetings are always on Wednesday. So, is there a way to modify my code so that it calculates the 6-Month Review Date as 180 days after the Approval_Date, but then rounds up to the next Wednesday?

I really appreciate the help.

Thanks,
Jim

 

[FoFa]

Use the weekday function to calculate the day your 6 months falls on, add however many extra days to get it to Wed.

 

[jereece]

Use the weekday function to calculate the day your 6 months falls on, add however many extra days to get it to Wed.

Unless I misunderstand you, this defeates what I am trying to do. I am trying for it to automatically calculate the 6-month date then automatically round it up to the next Wednesday. I don't want to have to manually add extra days to get the date to a Wednesday.

Any other suggestions? Thanks,
Jim

 

[raskew]

Hi -

The following will round-up a user input date to the next specified weekday.
If you had field [adate] you can add 180 days then round-up to the next Wednesday as follows:

adate = #12/25/06#
? fNextNthDay([adate] + 180, vbWednesday)
6/27/2007

The function:

Function fNextNthDay(DteStart As Date, _
                     intWeekday As Integer) As Date
'**************************************************
'Purpose:   Round date up to next specified
'           weekday
'Inputs:    1) ? fNextNthDay(#4/18/06#, vbWednesday)
'           2) ? fNextNthDay(#4/19/06#, vbWednesday)
'           3) ? fNextNthDay(#4/20/06#, vbWednesday)
'Output:    1) 4/19/06
'           2) 4/26/06
'           3) 4/26/06
'**************************************************
fNextNthDay = DteStart - WeekDay(DteStart) + _
              intWeekday + _
              IIf(WeekDay(DteStart) >= intWeekday, 7, 0)

End Function

HTH - Bob

 

[FoFa]

Unless I misunderstand you, this defeates what I am trying to do. I am trying for it to automatically calculate the 6-month date then automatically round it up to the next Wednesday. I don't want to have to manually add extra days to get the date to a Wednesday.

Any other suggestions? Thanks,
Jim

If you lookup the functin, you would see it is a simple mathimatical addition to do it automatically.

 

[jereece]

I am still really confused about how to do this.

FoFa: Are you saying make the field value something like "6-Month Review Date: [Approval_Date]+180+Weekday 4" or something like that? Wednesday is weekday 4, but I am not sure how to incorporate it into the field calculation.

raskew: Where do I store the function code in the query?

I really appreciate your help and appologize for not understanding.

Jim

 

[raskew]

Jim -

Place the function in a standard module and call it from your query as described.

Bob

 

[raskew]

Hi FoFa -

If you lookup the functin, you would see it is a simple mathimatical addition to do it automatically.

An example of the mathimatical functin you're describing would be helpful.

Bob

 

[jereece]

raskew - I created a new "module 1" and entered the function code. I then went to my query and entered the code in the Field section. However Access says the expression is invalid and highlights fNextNthDay.

Any suggestions? Thanks,
Jim

 

[FoFa]

I think just a function to man handle it like this:

Function NextWed(FromDate As Date) As Integer
  Dim Wrk As Integer
  Wrk = Weekday(FromDate, vbWednesday)
  ' Reverse values
  Select Case Wrk
    Case 7
      NextWed = 1
    Case 6
      NextWed = 2
    Case 5
      NextWed = 3
    Case 4
      NextWed = 4
    Case 3
      NextWed = 5
    Case 2
      NextWed = 6
    Case 1
      NextWed = 0
    Case 0
      NextWed = 0
 End Select
MsgBox NextWed
End Function

Then in your query you can DATEADD 180 + NextWed(somedate)