Defaulting to Saturday date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a field for a date to be input, and I want to create a query that
takes this date and returns another field with the date that Saturday. So if
Saturdays date is input it should show the same date, and any other should be
the next Saturday.

Any ideas please?
 
Hi -

Try copying this to a new module then calling as shown.

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/19/06
' 3) 4/26/06
'**************************************************
fNextNthDay = dteStart - WeekDay(dteStart) + _
intWeekday + _
IIf(WeekDay(dteStart) > intWeekday, 7, 0)

End Function

HTH - Bob
 
in your query, try adding the following calculated field, as

BumpedDate:[DateFieldName]+(7-DatePart("w",[DateFieldName]))

the above goes all on one line, regardless of linewrap in this post. replace
DateFieldName with the correct name of the date field in your table, of
course. suggest you read up on the DatePart() function, so you'll understand
how it works.

hth
 
Back
Top