G
Guest
I have the following code in a button on a user input form where user picks
date range, dock, delivery method & shift then clicks the button to create a
report. The filter is applied to the report and works great. The same
report contains a chart that uses a crosstab query as it's row source. I
have tried unsuccessfully for hours to write the correct syntax after
qdf_Chart.SQL= ... and cannot figure it out. I can write it correctly for a
select query, but not a crosstab query. Maybe I need to dim the parameters?
I've copies the SQL from the crosstab query and pasted below so you can see
what the query is showing. I just need to write the WHERE & strFIlter & in
the qdf_Chart.SQL and cannot. Thank you so much for your help.
Private Sub cmdApplyFilter_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim qdf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim strDock As String
Dim strShift As String
Dim strMethod As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
Dim strFilterChart
Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("RCVGDeliveryMethodQuery")
Set qdf_Chart = db.QueryDefs("RCVGDeliveryMethodTrucksCrosstab")
'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If
If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If
' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "RCVGDelMethodReport") <>
acObjStateOpen Then
DoCmd.OpenReport "RCVGDelMethodReport", acViewPreview
End If
' Build criteria string from method listbox
For Each VarItem In Me.cmdMethod.ItemsSelected
strMethod = strMethod & ",'" & Me.cmdMethod.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strMethod) = 0 Then
strMethod = "Like '*'"
Else
strMethod = Right(strMethod, Len(strMethod) - 1)
strMethod = "IN(" & strMethod & ")"
End If
'Build criteria sting from Shift listbox
For Each VarItem In Me.cmdShift.ItemsSelected
strShift = strShift & ",'" & Me.cmdShift.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strShift) = 0 Then
strShift = "Like '*'"
Else
strShift = Right(strShift, Len(strShift) - 1)
strShift = "IN(" & strShift & ")"
End If
'Build criteria string from Dock listbox
For Each VarItem In Me.cmddock.ItemsSelected
strDock = strDock & ",'" & Me.cmddock.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strDock) = 0 Then
strDock = "Like '*'"
Else
strDock = Right(strDock, Len(strDock) - 1)
strDock = "IN(" & strDock & ")"
End If
'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If
' Build filter string [name] is the table field name
strFilter = "[Shift]" & strShift & " AND [Dock] " & strDock & " AND
[DeliveryMethod] " & strMethod & " AND [DelDate] Between #" & datBeginDate &
"# and #" & datEndDate & "#"
' Apply the filter and switch it on
With Reports![RCVGDelMethodReport]
.Filter = strFilter
.FilterOn = True
.DelReportTitle.Value = "Receiving Method Dock " &
Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
'.DelChartReportTitle.Value = "Receiving Method Dock by Shift
" & Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
End With
qdf_Chart.SQL=???????????
'Crosstab SQL
PARAMETERS [forms]![DelMethodPickformTrucks]![cmdbegindate] DateTime,
[forms]![DelMethodPickformTrucks]![cmdenddate] DateTime,
[forms]![DelMethodPickformTrucks]![cmdDock] Text ( 255 ),
[forms]![DelMethodPickformTrucks]![cmdMethod] Text ( 255 ),
[forms]![DelMethodPickformTrucks]![cmdShift] Text ( 255 );
'TRANSFORM Sum(RCVGDeliveryMethodTrucksChart2.Trailers) AS SumOfTrailers
'SELECT RCVGDeliveryMethodTrucksChart2.Del_Date,
Sum(RCVGDeliveryMethodTrucksChart2.Trailers) AS [Total Of Trailers]
'FROM RCVGDeliveryMethodTrucksChart2
'GROUP BY RCVGDeliveryMethodTrucksChart2.Del_Date
'PIVOT RCVGDeliveryMethodTrucksChart2.Shift;
date range, dock, delivery method & shift then clicks the button to create a
report. The filter is applied to the report and works great. The same
report contains a chart that uses a crosstab query as it's row source. I
have tried unsuccessfully for hours to write the correct syntax after
qdf_Chart.SQL= ... and cannot figure it out. I can write it correctly for a
select query, but not a crosstab query. Maybe I need to dim the parameters?
I've copies the SQL from the crosstab query and pasted below so you can see
what the query is showing. I just need to write the WHERE & strFIlter & in
the qdf_Chart.SQL and cannot. Thank you so much for your help.
Private Sub cmdApplyFilter_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim qdf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim strDock As String
Dim strShift As String
Dim strMethod As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
Dim strFilterChart
Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("RCVGDeliveryMethodQuery")
Set qdf_Chart = db.QueryDefs("RCVGDeliveryMethodTrucksCrosstab")
'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If
If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If
' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "RCVGDelMethodReport") <>
acObjStateOpen Then
DoCmd.OpenReport "RCVGDelMethodReport", acViewPreview
End If
' Build criteria string from method listbox
For Each VarItem In Me.cmdMethod.ItemsSelected
strMethod = strMethod & ",'" & Me.cmdMethod.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strMethod) = 0 Then
strMethod = "Like '*'"
Else
strMethod = Right(strMethod, Len(strMethod) - 1)
strMethod = "IN(" & strMethod & ")"
End If
'Build criteria sting from Shift listbox
For Each VarItem In Me.cmdShift.ItemsSelected
strShift = strShift & ",'" & Me.cmdShift.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strShift) = 0 Then
strShift = "Like '*'"
Else
strShift = Right(strShift, Len(strShift) - 1)
strShift = "IN(" & strShift & ")"
End If
'Build criteria string from Dock listbox
For Each VarItem In Me.cmddock.ItemsSelected
strDock = strDock & ",'" & Me.cmddock.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strDock) = 0 Then
strDock = "Like '*'"
Else
strDock = Right(strDock, Len(strDock) - 1)
strDock = "IN(" & strDock & ")"
End If
'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If
' Build filter string [name] is the table field name
strFilter = "[Shift]" & strShift & " AND [Dock] " & strDock & " AND
[DeliveryMethod] " & strMethod & " AND [DelDate] Between #" & datBeginDate &
"# and #" & datEndDate & "#"
' Apply the filter and switch it on
With Reports![RCVGDelMethodReport]
.Filter = strFilter
.FilterOn = True
.DelReportTitle.Value = "Receiving Method Dock " &
Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
'.DelChartReportTitle.Value = "Receiving Method Dock by Shift
" & Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
End With
qdf_Chart.SQL=???????????
'Crosstab SQL
PARAMETERS [forms]![DelMethodPickformTrucks]![cmdbegindate] DateTime,
[forms]![DelMethodPickformTrucks]![cmdenddate] DateTime,
[forms]![DelMethodPickformTrucks]![cmdDock] Text ( 255 ),
[forms]![DelMethodPickformTrucks]![cmdMethod] Text ( 255 ),
[forms]![DelMethodPickformTrucks]![cmdShift] Text ( 255 );
'TRANSFORM Sum(RCVGDeliveryMethodTrucksChart2.Trailers) AS SumOfTrailers
'SELECT RCVGDeliveryMethodTrucksChart2.Del_Date,
Sum(RCVGDeliveryMethodTrucksChart2.Trailers) AS [Total Of Trailers]
'FROM RCVGDeliveryMethodTrucksChart2
'GROUP BY RCVGDeliveryMethodTrucksChart2.Del_Date
'PIVOT RCVGDeliveryMethodTrucksChart2.Shift;