Crosstab Report

Hi,

I have a table on which I would like to produce the following report (All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date range
that is chosen the weekNo changes and therefore the report will not run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it is an
every changing field?

Thanks

This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate. Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the query or
report.


--
Duane Hookom
MS Access MVP


"news.microsoft.com" wrote in message
...
Hi,

I have a table on which I would like to produce the following report (All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date range
that is chosen the weekNo changes and therefore the report will not run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it is an
every changing field?

Thanks

Thanks for this but people need to see the Week No.
Is there no way of doing this?
Could I code something in the on format of the report?
eg taking the entered date and then working backwards to get the week number
and putting it in a unbound textbox?
What do you?
Any help greatly appreciated

"Duane Hookom" wrote in message
...
This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form
to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate.
Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the query
or
report.


--
Duane Hookom
MS Access MVP


"news.microsoft.com" wrote in message
...
Hi,

I have a table on which I would like to produce the following report
(All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date range
that is chosen the weekNo changes and therefore the report will not run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it is
an
every changing field?

Thanks

Use the same type of expressions for you column headings in the report. Use
text boxes with control sources like:
=DateAdd("ww",0,[Forms]![frmA]![txtEndDate])
=DateAdd("ww",-1,[Forms]![frmA]![txtEndDate])
=DateAdd("ww",-2,[Forms]![frmA]![txtEndDate])
This should create column "labels" that vary depending on the date entered
on the form.

--
Duane Hookom
MS Access MVP
--

"news.microsoft.com" wrote in message
...
Thanks for this but people need to see the Week No.
Is there no way of doing this?
Could I code something in the on format of the report?
eg taking the entered date and then working backwards to get the week
number
and putting it in a unbound textbox?
What do you?
Any help greatly appreciated

"Duane Hookom" wrote in message
...
This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form
to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate.
Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the
query
or
report.


--
Duane Hookom
MS Access MVP


"news.microsoft.com" wrote in message
...
Hi,

I have a table on which I would like to produce the following report
(All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date
range
that is chosen the weekNo changes and therefore the report will not
run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it
is
an
every changing field?

Thanks