Crosstab query for 7 days in future

[lewispb]

I have a cross tab query set up to display the quantity of products ordered each date.
I want the query to dynamically show only the future 7 days as columns. So the query would return:
ProductName, 10/06/2010, 11/06/2010, 12/06/2010.
TestProduct, 0, 10, 25
I've tried using the PIVOT IN command, but am not making progress.

This is what I have so far:
TRANSFORM Sum(qryOrdersWithDate.Quantity) AS SumOfQuantity
SELECT qryOrdersWithDate.ProductName AS Product
FROM qryOrdersWithDate
GROUP BY qryOrdersWithDate.ProductName
PIVOT Format([OrderDate],"Short Date");

Any help much appreciated

 

[JamesMcS]

Just put a criteria in the date field to say "between now() and now()+7"

 

[lewispb]

That's awesome thanks very much.
I'd like to display the 7 date columns and all products even if the values are null. Any ideas?

 

[JamesMcS]

The only thing I can think of is to have the dates as column headers and define the columns in properties in query design. But, they wouldn't be dynamic.

The way to get around THAT would be to copy the SQL statement into VBA and substitue variables for the column headers, i.e. format(now(),"dd/mm/yyyy"), format(now()+1,"dd/mm/yyyy" and so on. Unless you can put variables in the query design - not tried it but I don't think so.

 

[lewispb]

Great! I thought about using variables etc but had no success. I would like the headings to be dynamic. How do I use VBA to run the query?

 

[JamesMcS]

What I usually do is create the query as normal, switch to SQL view, copy the SQL code and paste into the VBA editor, using the command docmd.runsql (have a look in the help file).

I forget the exact syntax for column definition in SQL but if it looks like this:

IN "01/06/2010", "08/06/2010" yada yada

You'd put

IN " & format (now(),'dd/mm/yyyy/') & ", " & format (now()+7,'dd/mm/yyyy') & ", " yada yada

Notice I've use apostrophes in the place of the quotes that appear in the SQL statement. In VBA you have to use either apostrophes or double quotes, otherwise it'll think it's the end of (or a break in) the statement.

 

[lewispb]

Got that. I'd like to display this cross tab query on a form. Which event do I attatch the docmd.runsql code to to display the query on a form?

 

[JamesMcS]

Probably best off just basing a subform on the query. That way it'll run whenever the form opens.

 

[lewispb]

Thanks for all your suggestions. I've already hit your scales

This is the VBA code I have so far, but I cannot for the life of me get it to produce a crosstab query in a datasheet subform.

Private Sub Form_Load()
Dim coldate(7) As String
For i = 0 To 6
coldate(i) = Format(Now() + i, "dd/mm/yyyy")
Next i
Dim sqlstring As String
sqlstring = sqlstring & "TRANSFORM Sum(qryOrdersWithDate.Quantity) AS SumOfQuantity "
sqlstring = sqlstring & "SELECT qryOrdersWithDate.ProductName AS Product "
sqlstring = sqlstring & "FROM qryOrdersWithDate WHERE (((Format([OrderDate],'Short Date')) Between Now() And Now()+7)) "
sqlstring = sqlstring & "GROUP BY qryOrdersWithDate.ProductName "
sqlstring = sqlstring & "PIVOT Format([OrderDate],'Short Date') IN ('" & coldate(1) & "','" & coldate(2) & "','" & coldate(3) & "','" & coldate(4) & "','" & coldate(5) & "','" & coldate(6) & "','" & coldate(7) & "');"
Me![ManageOrdersCTSF].Form.RecordSource = sqlstring
End Sub

What am I doing wrong?!

 

[JamesMcS]

Good thinking on the for loop but I think just putting the format(now()+1), +2 and so on would be the way to go. Try that and see if it works....

 

[lewispb]

Thanks for all your help.
My final, working VBA code looks like this. It is attatched to the subform.

Private Sub Form_Load()
Dim coldate(6) As String
For i = 0 To 6
coldate(i) = Format(Now() + i, "dd/mm/yyyy")
Next i
lbl0.Caption = coldate(0)
lbl1.Caption = coldate(1)
lbl2.Caption = coldate(2)
lbl3.Caption = coldate(3)
lbl4.Caption = coldate(4)
lbl5.Caption = coldate(5)
lbl6.Caption = coldate(6)
txt0.ControlSource = coldate(0)
txt1.ControlSource = coldate(1)
txt2.ControlSource = coldate(2)
txt3.ControlSource = coldate(3)
txt4.ControlSource = coldate(4)
txt5.ControlSource = coldate(5)
txt6.ControlSource = coldate(6)
Dim sqlstring As String
sqlstring = sqlstring & "TRANSFORM Sum(qryOrdersWithDate.Quantity) AS SumOfQuantity "
sqlstring = sqlstring & "SELECT qryOrdersWithDate.ProductName AS Product "
sqlstring = sqlstring & "FROM qryOrdersWithDate WHERE (Format([OrderDate],'Short Date')) BETWEEN (Format(Now(),'Short Date')) AND (Format(Now() + 6,'Short Date')) "
sqlstring = sqlstring & "GROUP BY qryOrdersWithDate.ProductName "
sqlstring = sqlstring & "PIVOT Format([OrderDate],'Short Date') IN ('" & coldate(0) & "','" & coldate(1) & "','" & coldate(2) & "','" & coldate(3) & "','" & coldate(4) & "','" & coldate(5) & "','" & coldate(6) & "');"
Me.RecordSource = sqlstring
End Sub

 

[JamesMcS]

Nice one! Glad it works finally! A good feeling on a Friday afternoon...

I've never seen a bracketed number after a variable declaration before. What does that mean?

 

[lewispb]

Indeed!
The bracketed number is for an array. I am very new to VBA etc but have quite a lot of VB.NET experience so I bring that with me I suppose.

See here for a quick overview:
http://www.w3schools.com/VBscript/vbscript_variables.asp
Under VBScript Array Variables.

Thanks once again!

 

[JamesMcS]

Cheers, I'm in on Sunday so I'll have a butcher's then!

Enjoy the footie if you watch it... I'll be poking myself in the eye with a pencil

 

[vbaInet]

http://www.w3schools.com/VBscript/vbscript_variables.asp

@JamesMcS: A much relevant resource for vba arrays:

http://www.cpearson.com/excel/VBAArrays.htm

Re "MVP Wannabe", speak to boblarson to see what he can do for you lol

 

[JamesMcS]

Cheers VBA I'll have a look at that this morning! Don't think I'm quite there with the MVP thing.... one day though!