Prev: Basic way to make basic databases work on Access2007?
Next: From temporary table to master table and then join this in junction table
From: Alan on 8 Apr 2010 09:16
How do i pull two pivot value into there according text boxes

i have this code

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS Y"
strSQL = strSQL & " SELECT [" & TempVars![Display1] & "] as
SalesGroupingField2 FROM [1a11f raw] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year1]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By1] & "], [" &
TempVars![Display1] & "]"
strSQL = strSQL & " Pivot [1a11f raw].[Quarter] in (1,2,3,4)"

i have text box name 1 2 3 4 and the value for top code goes in
but in the second part of the code the pivot says 1,2,3,4 my boxes are
1f,2f,3f,4f but i cannot use that because i doesn't reckonise the quarter
field

How i can i use two pivot fields
 | 
Pages: 1
Prev: Basic way to make basic databases work on Access2007?
Next: From temporary table to master table and then join this in junction table