how to get the next monday?

[lbbeurmann]

I am trying to build a query that shows the projected start dates for
students in a class. each class starts on the monday 2 weeks after they sign
up. i am not able to modify the tables to add in a projected start date for
each student, but i wanted to run a query and base it on their sign-up date.
the problem is that not all students sign-up on a monday, so using a dateadd
of 2 weeks does not give a valid class start date. is there another function
that i can use to calculate this in my query? any help is greatly
appreciated.

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through 22. Use your table and field names for Dates_LeRoy and ID.

SELECT Dates_LeRoy.ID, Dates_LeRoy.SignUp, DateAdd("d",[CountNUM],[SignUp])
AS [Class Date]
FROM CountNumber, Dates_LeRoy
GROUP BY Dates_LeRoy.ID, Dates_LeRoy.SignUp,
Weekday(DateAdd("d",[CountNUM],[SignUp])), CountNumber.CountNUM,
DateAdd("d",[CountNUM],[SignUp])
HAVING (((Weekday(DateAdd("d",[CountNUM],[SignUp])))=2) AND
((CountNumber.CountNUM) Between 14 And 21))
ORDER BY Dates_LeRoy.ID;

 

[lbbeurmann]

Thanks Bob. This worked perfectly.

Hi -

Try:
? x - weekday(x) + 16
...where x = the signup date.

HTH - Bob

You can write a vba public function that returns the correct result to you.
Call that function in your query.

SELECT studid, getMonday2WksFromNow(SignupDate) AS StartDate
FROM students;

In a standard module:

Public Function getMonday2WksFromNow(dtSignup As Date) As Date
On Error GoTo ProcErr

Select Case Weekday(dtSignup)
Case 1
getMonday2WksFromNow = dtSignup + 8
Case 2
getMonday2WksFromNow = dtSignup + 14
Case 3
getMonday2WksFromNow = dtSignup + 13
Case 4
getMonday2WksFromNow = dtSignup + 12
Case 5
getMonday2WksFromNow = dtSignup + 11
Case 6
getMonday2WksFromNow = dtSignup + 10
Case 7
getMonday2WksFromNow = dtSignup + 9
End Select

Exit Function
ProcErr:
MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function

Chris

I am trying to build a query that shows the projected start dates for
students in a class. each class starts on the monday 2 weeks after they sign

[quoted text clipped - 4 lines]

that i can use to calculate this in my query? any help is greatly
appreciated.

 

[Bob Quintal]

Create a table named CountNumber with field CountNUM containing
numbers from 0 (zero) through 22. Use your table and field names
for Dates_LeRoy and ID.

SELECT Dates_LeRoy.ID, Dates_LeRoy.SignUp,
DateAdd("d",[CountNUM],[SignUp]) AS [Class Date]
FROM CountNumber, Dates_LeRoy
GROUP BY Dates_LeRoy.ID, Dates_LeRoy.SignUp,
Weekday(DateAdd("d",[CountNUM],[SignUp])), CountNumber.CountNUM,
DateAdd("d",[CountNUM],[SignUp])
HAVING (((Weekday(DateAdd("d",[CountNUM],[SignUp])))=2) AND
((CountNumber.CountNUM) Between 14 And 21))
ORDER BY Dates_LeRoy.ID;

Wow, can I please use this as an example for my students of how to turn
a simple one line function into a terrible waste of disk space,
computer cycles and debugging time?

 

[John Spencer]

I believe the following expression will generate the desired date.

DateAdd("d",15-WeekDay(X,2),X)

Test it with several different dates and see if it yields the correct result

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================