Query : Week Label?

[GUIDO22]

I have a report that will generate the current list of employees including the (following) week number and the week ending date. When the report is run, the users enters the date for which the report is to run from. The results it generates are correct for the week following the date the user entered.

However, my users want the ability to enter 'x' weeks as an additional parameter so that they can print off 'x' number of weeks in one hit? I am not too sure of the best way to do this.

The code will need to present a list of 'x' weeks results to a report used to print labels. eg. : when todays date = 5/1/05, weeks = 3: query will display

Employee: Joe
Week No.2
Week ending : 16/1/05

Employee: Joe
Week No.3
Week ending : 23/1/05

Employee: Joe
Week No.4
Week ending : 31/1/05

for each staff member .........

Presently, my report uses a call to a function at 'Module' level to generate the week ending date, the week no. is generated at 'Query' level.

Any ideas on the best way to do this?

NB.This information is used to generate staff labels - to avoid gaps in the label sheets the list needs to be continuous i.e. (not looping sending 'x' print jobs to the printer).



TIA
Guido

 

[M8KWR]

You are best using the dateadd function, here is an example

Dim FirstDate As Date   ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "ww"  ' ww" specifies weekd as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of Weeks to add")
Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg

If you are stuck putting this into you code please let me know. Ignore all the msgbox and inputboxes it is just to show you how it all works before you insert your own control names etc.

Hope this helps.

 

[GUIDO22]

Thanks - but this is not exactly what I need! Ido know about this function and indeed; am already using it to generate the week ending date in my code module!

My dilemma is simply knowing how & where to integrate the 'x' no. weeks parameter entered by the user, into either the query OR report OR code module to generate the listings as descibed. Is this clear?