Showing The Date range instead of Week Number

[Scottigus]

I am making a Query where I am trying to sort it by week range. Such as January 1st - January 7th = Week 1. I have the report made up (I used the wizard) and it shows it as 1, 2, 3, 4. It might be hard for people to follow what the weeks are and what range there referring to. Is there any way to change the 1 2 3, etc. to 1/1/2009 - 1/7/2009, etc.?

Thanks for the help...

 

[Alansidman]

Are you looking to go 1,2,3,4 for January and 1,2,3,4 for February, etc. or are you looking to go 1,2,3,4,5,6,7.......etc?

Alan

 

[HiTechCoach]

I would use these:

' The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

' The last day of the current week:
Date() - WeekDay(Date()) + 7

' The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1

' The last day of the current week:
Date() - WeekDay(Date(), 0) + 7

It will be hard to say the best way to do it without more information about the fields in the query and how you are getting the week number.

 

[Scottigus]

Well, when I run the report wizard and create a report, I choose Bid Date (I work for a company where we bid a lot, so there are many bid dates). I then just go to the option below that where you can sort by Month, Week, Day, Hour, minute, etc. and choose week.

But what I want it to do is say Between the 1st week of the year (example for this year is 1/6/2008 - 1/12/2008. That would be week 1. Week 2 would be 1/13/2008 - 1/19/2008, etc. etc. So it would see the 1st full week of the year and start there at 1.

So it would read Week 1 - 1/6/2008 - 1/12/2008.
Week 2 - 1/13/2008 - 1/19/2008
etc.
etc.

That is my goal. The reason is that we might have 4 bids in week 1, and I already have it set up to calculate all bids within weeks. So the math part is all done, it is just getting the 3 that the report shows to show the actual date. That is my question...if it makes any sense...

 

[jal]

You said you used a wizard. Maybe go into SQL view to get a copy of the query produced by the wizard, and paste it here. Maybe we can edit the query as to make it display the output in your desired format.

 

[raskew]

Hi -

Try incorporating this:

Public Function fWkNumToDate(pWkNum As Integer, pYear As Integer) As String
'Purpose:   Return the start and end dates of a week
'           based on the year and week number (based on
'           the first full week of the year, starting on
'           a Sunday).
'Coded by:  raskew
'Inputs:    1) ? fWkNumToDate(1, 2008)
'           2) ? fWkNumToDate(32, 2008)

'Outputs:   1) 1/6/2008 - 1/12/2008
'           2) 8/10/2008 - 8/16/2008

Dim dteStart As Date
Dim dteHold  As Date
Dim intHold  As Integer
 
   dteHold = DateSerial(pYear, 1, 1)
   intHold = WeekDay(dteHold)
   dteStart = IIf(intHold <> 1, dteHold - intHold + 8, dteHold)
   
   fWkNumToDate = dteStart + (7 * ((pWkNum) - 1)) & " - " & dteStart + (7 * (pWkNum) - 1)

End Function

HTH - Bob