You might check out this posting ----
:
I'm posting this in case someone in the future needs this same thing. Notice
in my original post that I was looking to change _either_ the field name in
the query OR the labels in the report based on that query.
I found the following page on Microsoft's Developer's Network website:
http://msdn.microsoft.com/en-us/library/aa172343(office.11).aspx
Basically it walks you through setting up a macro that will rename your
controls in a report (or form) based on an expression. So, I created a report
based on my original query (the one that had the label names "m2", "m3" etc.
that I didn't like), then created a macro that opened that report in layout
view and renamed those labels using Karl's suggested DateAdd function. It
looks like this:
OpenReport qry_ForecastChanges, Layout, , , Normal
SetValue [Reports]![qry_ForecastChanges]![m2_Label].[Caption],
Format(DateAdd("m",1,(Date())),"mmm-yy")
SetValue [Reports]![qry_ForecastChanges]![m3_Label].[Caption],
Format(DateAdd("m",2,(Date())),"mmm-yy")
.....
SetValue [Reports]![qry_ForecastChanges]![m26_Label].[Caption],
Format(DateAdd("m",25,(Date())),"mmm-yy")
OpenReport qry_ForecastChanges, Report, , , Normal
Thanks again, Karl, for setting me on the right path!!
Ryan C.
--
KARL DEWEY
Build a little - Test a little
MarieT said:
Thank you Karl. This definitely worked! I have a question though, Is it
possible to do the same thing with a Select Query instead of a crosstab; What
I mean is: Is there a way to define the column header dynamically depending
on the date, and do this in a Select query? In other word, can I make the
column "label" that precedes a : (colon) be evaluated as an expression and
not a constant?
If there is a way to do this, I will then be able to define my 12 columns
dynamically relative to today's date, in a Select Query.
Thank you so much!
MarieT
KARL DEWEY said:
First create a table named CountNumber with field named CountNUM with numbers
from 0 (zero) through your maximum spread.
Your DateAdd("d",1-Weekday(Date()),Date()) gave me 6/1/2008 as first column
in this query --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],DateAdd("d",1-Weekday(Date()),Date()));
My Date()-Format(Date(),"w")+2 gave me 6/2/2008 but is not fully tested.
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date()-Format(Date(),"w")+2);
This gives 6/3/2008, today's date --
TRANSFORM Sum([QTY]/12) AS Expr2
SELECT MarieT.Part
FROM MarieT, CountNumber
WHERE (((CountNumber.CountNUM)<=11))
GROUP BY MarieT.Part
PIVOT DateAdd("ww",[CountNUM],Date());
--
KARL DEWEY
Build a little - Test a little
MarieT said:
Hello,
I have a table with a Qty value for each part. I need to create a query with
12 coulmn headings, where each column heading is the date of the first day of
the week for the next 12 weeks, and the Value I want under each column date
is the Qty value divided by 12. So under each week coulmn I should get the
same value (that is the Qty/12). For example:
If in the table I have:
Part Qty
Part1 84000
Part2 96000
The query will have:
Part 06/02/2008 06/09/2008 06/16/2008 .....
Part1 7000 7000 7000
Part2 8000 8000 8000
I know how to find the first week day of the current date:
Expr1: DateAdd("d",1-Weekday(Date()),Date())
But this only gives me one column, and I can only specify one column
heading. How can I make it give me 12 columns with the correct headings and
the correct same value under each coulmn? This sounds pretty simple, yet I
have not yet found a way to do it.
Any help will be greatly appreciated!
MarieT
