A
Alex
The following code opens an unbound report and applies selected values that
are picked on an unbound user pick form containing multi-value list boxes to
the query filter. The below code works great. I now need to add a total
field on the same report. I've tried several ways to do this with no
success. I created a new select query called TotalQry which has the
following SQL: SELECT Count(NewHireQuery.PeopleSoftID) AS CountOfPeopleSoftID
FROM NewHireQuery INNER JOIN DistinctLengthCategory ON
NewHireQuery.LengthCategory = DistinctLengthCategory.LengthCategory
WHERE (((NewHireQuery.GenderDesc) Like '*') AND
((NewHireQuery.EthnicDescription) Like '*') AND ((NewHireQuery.HireDate)
Between #2/1/2004# And #5/1/2004#));
I've added a control in the report header and have tried creating a form to
show the value and assign the form control to the unbound report control.
I've also tried to make the unbound report bound to the TotalQry. Nothing is
working. I think I ultimately need to have to below code set another query
(TotalQry) and add the SQL to the bottom of this code, but I've tried and
have no success. Can someone please help me? Thanks much!
Private Sub RunChartCodeLength_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim adf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim StrGender As String
Dim StrEthnic As String
Dim datBegin As Date
Dim datEnd As Date
Dim strFilter As String
Dim StrFilterChart
Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("ChartCodeLengthNH")
Set qdf_Chart = db.QueryDefs("ChartCodeLengthNH")
'Check for Begin and End Date
If Len(Me.cmdBegin.Value & "") = 0 Then
MsgBox "You must type a beginning Hire Date"
Exit Sub
End If
If Len(Me.cmdEnd.Value & "") = 0 Then
MsgBox "You must type an ending Hire Date"
Exit Sub
End If
'Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "ReportCodeLengthNH") <>
acObjStateOpen Then
DoCmd.OpenReport "ReportCodeLengthNH", acViewPreview
End If
'Build Criteria string from Gender Listbox
For Each VarItem In Me.cmdGender.ItemsSelected
StrGender = StrGender & ",'" & Me.cmdGender.ItemData(VarItem) & "'"
Next VarItem
If Len(StrGender) = 0 Then
StrGender = "Like'*'"
Else
StrGender = Right(StrGender, Len(StrGender) - 1)
StrGender = "IN(" & StrGender & ")"
End If
'Build Criteria string from Ethnic Listbox
For Each VarItem In Me.cmdEthnic.ItemsSelected
StrEthnic = StrEthnic & ",'" & Me.cmdEthnic.ItemData(VarItem) & "'"
Next VarItem
If Len(StrEthnic) = 0 Then
StrEthnic = "Like'*'"
Else
StrEthnic = Right(StrEthnic, Len(StrEthnic) - 1)
StrEthnic = "IN(" & StrEthnic & ")"
End If
'Build Beginning and Ending date parameter
If Not IsNull(Me.cmdBegin) Then
datBegin = Me.cmdBegin
End If
If Not IsNull(Me.cmdEnd) Then
datEnd = Me.cmdEnd
End If
'Build filter string [name] is the table field name
strFilter = "[GenderDesc]" & StrGender & _
"AND [EthnicDescription]" & StrEthnic & _
"AND [HireDate] Between #" & datBegin & "# and #" & datEnd & "#"
'Apply the filter and switch it on
With Reports![ReportCodeLengthNH]
..Filter = strFilter
..FilterOn = True
..TitleDate.Value = "New Hire Report for " & Me.cmdBegin.Value & "-" & _
Me.cmdEnd.Value
If StrGender = "Like'*'" Then .TitleGender.Value = "All Genders" Else
..TitleGender.Value = "Gender: " & StrGender
If StrEthnic = "Like'*'" Then .TitleEthnic.Value = "All Ethnic Groups" Else
..TitleEthnic.Value = "Ethnics: " & StrEthnic
End With
qdf_Chart.SQL = "TRANSFORM Count(NewHireQuery.PeopleSoftID) AS
CountOfPeopleSoftID SELECT NewHireQuery.LengthCategory FROM
DistinctLengthCategoryNH INNER JOIN NewHireQuery ON
DistinctLengthCategoryNH.LengthCategory = NewHireQuery.LengthCategory WHERE"
& strFilter & " GROUP BY NewHireQuery.LengthCategory,
DistinctLengthCategoryNH.LengthOrder ORDER BY
DistinctLengthCategoryNH.LengthOrder PIVOT NewHireQuery.Code"
End Sub
are picked on an unbound user pick form containing multi-value list boxes to
the query filter. The below code works great. I now need to add a total
field on the same report. I've tried several ways to do this with no
success. I created a new select query called TotalQry which has the
following SQL: SELECT Count(NewHireQuery.PeopleSoftID) AS CountOfPeopleSoftID
FROM NewHireQuery INNER JOIN DistinctLengthCategory ON
NewHireQuery.LengthCategory = DistinctLengthCategory.LengthCategory
WHERE (((NewHireQuery.GenderDesc) Like '*') AND
((NewHireQuery.EthnicDescription) Like '*') AND ((NewHireQuery.HireDate)
Between #2/1/2004# And #5/1/2004#));
I've added a control in the report header and have tried creating a form to
show the value and assign the form control to the unbound report control.
I've also tried to make the unbound report bound to the TotalQry. Nothing is
working. I think I ultimately need to have to below code set another query
(TotalQry) and add the SQL to the bottom of this code, but I've tried and
have no success. Can someone please help me? Thanks much!
Private Sub RunChartCodeLength_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim adf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim StrGender As String
Dim StrEthnic As String
Dim datBegin As Date
Dim datEnd As Date
Dim strFilter As String
Dim StrFilterChart
Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("ChartCodeLengthNH")
Set qdf_Chart = db.QueryDefs("ChartCodeLengthNH")
'Check for Begin and End Date
If Len(Me.cmdBegin.Value & "") = 0 Then
MsgBox "You must type a beginning Hire Date"
Exit Sub
End If
If Len(Me.cmdEnd.Value & "") = 0 Then
MsgBox "You must type an ending Hire Date"
Exit Sub
End If
'Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "ReportCodeLengthNH") <>
acObjStateOpen Then
DoCmd.OpenReport "ReportCodeLengthNH", acViewPreview
End If
'Build Criteria string from Gender Listbox
For Each VarItem In Me.cmdGender.ItemsSelected
StrGender = StrGender & ",'" & Me.cmdGender.ItemData(VarItem) & "'"
Next VarItem
If Len(StrGender) = 0 Then
StrGender = "Like'*'"
Else
StrGender = Right(StrGender, Len(StrGender) - 1)
StrGender = "IN(" & StrGender & ")"
End If
'Build Criteria string from Ethnic Listbox
For Each VarItem In Me.cmdEthnic.ItemsSelected
StrEthnic = StrEthnic & ",'" & Me.cmdEthnic.ItemData(VarItem) & "'"
Next VarItem
If Len(StrEthnic) = 0 Then
StrEthnic = "Like'*'"
Else
StrEthnic = Right(StrEthnic, Len(StrEthnic) - 1)
StrEthnic = "IN(" & StrEthnic & ")"
End If
'Build Beginning and Ending date parameter
If Not IsNull(Me.cmdBegin) Then
datBegin = Me.cmdBegin
End If
If Not IsNull(Me.cmdEnd) Then
datEnd = Me.cmdEnd
End If
'Build filter string [name] is the table field name
strFilter = "[GenderDesc]" & StrGender & _
"AND [EthnicDescription]" & StrEthnic & _
"AND [HireDate] Between #" & datBegin & "# and #" & datEnd & "#"
'Apply the filter and switch it on
With Reports![ReportCodeLengthNH]
..Filter = strFilter
..FilterOn = True
..TitleDate.Value = "New Hire Report for " & Me.cmdBegin.Value & "-" & _
Me.cmdEnd.Value
If StrGender = "Like'*'" Then .TitleGender.Value = "All Genders" Else
..TitleGender.Value = "Gender: " & StrGender
If StrEthnic = "Like'*'" Then .TitleEthnic.Value = "All Ethnic Groups" Else
..TitleEthnic.Value = "Ethnics: " & StrEthnic
End With
qdf_Chart.SQL = "TRANSFORM Count(NewHireQuery.PeopleSoftID) AS
CountOfPeopleSoftID SELECT NewHireQuery.LengthCategory FROM
DistinctLengthCategoryNH INNER JOIN NewHireQuery ON
DistinctLengthCategoryNH.LengthCategory = NewHireQuery.LengthCategory WHERE"
& strFilter & " GROUP BY NewHireQuery.LengthCategory,
DistinctLengthCategoryNH.LengthOrder ORDER BY
DistinctLengthCategoryNH.LengthOrder PIVOT NewHireQuery.Code"
End Sub