Need Monday date for given week number

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I have a query where I am determining when a
supervisor has not completed a task in a week.
What I need to be able to do, given the week
number is determine the Monday date for the
week. I have the week number but I am unable
to find a way to give me this information. I found
the following function:

Public Function GetDateByYearWeekNumWeekDay(iYear As Integer, _
iWeekNum As Integer, iWeekDayNum As Integer) As Date

Dim vdate As Date

vdate = DateSerial(iYear, 1, 1) + ((iWeekNum - 1) * 7)
GetDateByYearWeekNumWeekDay = DateValue(vdate - Weekday(vdate) +
iWeekDayNum)

End Function

and put this in my query:

WeekOf: GetDateByYearWeekNumWeekDay(2010,[WeekID],[vbMonday])

I get a parameter pop-up for vbMonday.

Can anyone help me get this to work? Thanks!
 
and put this in my query:

WeekOf: GetDateByYearWeekNumWeekDay(2010,[WeekID],[vbMonday])

I get a parameter pop-up for vbMonday.
Click to expand...

Replace [vbMonday] - which will be interpreted as either a form reference or a
parameter requiring a prompt - with 2, its numeric value.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
it's weird, I tried that and I still got the parameter
pop-up.... I will check it again in the morning.
 
it's weird, I tried that and I still got the parameter
pop-up.... I will check it again in the morning.
Click to expand...

If it doesn't help, please post the complete SQL of the query. If you're
getting a prompt for vbMonday then you have [vbMonday] - a VBA variable which
SQL does not know and will not recognize - somewhere in the query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
You're right, I did. Found the problem with fresh eyes
this morning. Thank you so much for your help :-)
 
Back
Top