Sales Summary Date Range Filter Question?

[niuginikiwi]

Hi all,
I have a select query with the totals row enabled. 4 tables are
involved in that query (tblOrders, tblOrderDetails, tblProducts,
tblPacks)
tblOrders ---<tblOrderdetails and tblProducts ---<tblOrderDetails>---
tblPacks.
I have fields CustomerID from tblOrders, ProductName from tblProducts,
PackName from tblPacks and finally Qty from tblOrderDetails. All
fields are set as Group By except Qty which is set to Sum on the
Totals Row.
I use that query to produce a report.
In the report I have in the sorting and grouping dialog 2 fields
( CustomerID both header footer set to yes and ProductName only header
set to yes) and in the details section I have two fields ( sumofqty
and PackName).
This gives me a report in the form of , eg:

Customer ID: 3
ProductName: Lettuce
SumOfQty PackName
100 F75

That nicely summaries and its presents data in the form I am looking
for but when I put in the OrderDate field from tblOrders in the query
so I could later filter by date range to get that nice summary but
then the query tries to group records by OrderDate and in turn
presents me with a detailed information instead of the one I had
before.
What can I do to still keep the report look like what I was doing
before but be able to filter by Date Range.
Thanks,
niuginikiwi

 

[John W. Vinson]

That nicely summaries and its presents data in the form I am looking
for but when I put in the OrderDate field from tblOrders in the query
so I could later filter by date range to get that nice summary but
then the query tries to group records by OrderDate and in turn
presents me with a detailed information instead of the one I had
before.
What can I do to still keep the report look like what I was doing
before but be able to filter by Date Range.

Change the "totals" operation on the date field from Group By to Where.


John W. Vinson [MVP]

 

[niuginikiwi]

Hi John,
I have realised that and can use the criteria row to reference the
Date Range ( Between [Forms]![frmOrdersCrit2]![txtStartDate] And
[Forms]![frmOrdersCrit2]![txtEndDate]) that I enter from the criteria
form.
But here I am trying to do it a little differently coz I also use
other criteria to filter the report as well.

Here is my SQL that works fine:

SELECT tblCustomers.CustomerName, Sum(tblOrderDetails.PackQty) AS
SumOfPackQty, tblOrderDetails.CropOrdered, tblPacks.PackName,
tblOrders.CustomerID
FROM tblPacks INNER JOIN ((tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) INNER JOIN
tblOrderDetails ON tblOrders.OrderID = tblOrderDetails.OrderID) ON
tblPacks.PackID = tblOrderDetails.PackID
GROUP BY tblCustomers.CustomerName, tblOrderDetails.CropOrdered,
tblPacks.PackName, tblOrders.CustomerID;

But when I add the OrderDate field from tblOrders, it tries to group
every record by the OrderDate thus I get a different result which I
don't want.
I also use a command button that has the criteria string to filter the
report. Therefore, I want to build all criteria as a string on the
command button click event and avoid having another one just doing the
OrderDate explicitly on the query design ie to avoid using:
Between [Forms]![frmOrdersCrit2]![txtStartDate] And [Forms]!
[frmOrdersCrit2]![txtEndDate]
in the query design.

Here is the criteria string behind the command button:

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click

Dim cbo As ComboBox
Dim lst As ListBox
'Dim strDateField As String 'Name of your criteria date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

Dim strWhere As String
Dim iLen As Integer
Dim strDocName As String

strDocName = "rptSalesByMarket"


Set lst = Me.lstCrops
If lst.ItemsSelected.Count > 0 Then
strWhere = strWhere & "(CropOrdered" &
MultiSelectSQL(Me.lstCrops, """") & ") AND "
End If



Set lst = Me.lstCustomer
If lst.ItemsSelected.Count > 0 Then
strWhere = strWhere & "(CustomerID" &
MultiSelectSQL(Me.lstCustomer) & ") AND "
End If

'Date field. Use the format string to add the # delimiters and get the
right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([OrderDate] >= " &
Format(Me.txtStartDate, conDateFormat) & ") AND "
End If

' 'Another date field. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next
day.' strWhere = strWhere & "([OrderDate] < " &
Format(Me.txtEndDate + 1, conDateFormat) & ") AND "
End If



'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Sorry! MUST select at least a criteria value",
vbInformation, "No Criteria Values"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True
'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDocName).IsLoaded Then
DoCmd.Close acReport, strDocName
End If
'Open the report and apply filter.
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
Me.Visible = False

End If

Set cbo = Nothing
Set lst = Nothing


'Code below generated by MS Access Wizzard
Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox "Operation can not be executed, Try again"
Resume Exit_btnPreview_Click

End Sub

Thanks in advance for any alternatives.

niuginikiwi

 

[John W. Vinson]

But when I add the OrderDate field from tblOrders, it tries to group
every record by the OrderDate thus I get a different result which I
don't want.

Well.... if you don't want to group by the date, *don't Group By the date*. Do
as I suggested: use the "Where" pseudo-total operator. It is NOT necessary to
Group By a field in order to use it as a criterion!

The SQL would be

SELECT tblCustomers.CustomerName, Sum(tblOrderDetails.PackQty) AS
SumOfPackQty, tblOrderDetails.CropOrdered, tblPacks.PackName,
tblOrders.CustomerID
FROM tblPacks INNER JOIN ((tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) INNER JOIN
tblOrderDetails ON tblOrders.OrderID = tblOrderDetails.OrderID) ON
tblPacks.PackID = tblOrderDetails.PackID
WHERE tblOrders.OrderDate Between [Forms]![frmOrdersCrit2]![txtStartDate] And
[Forms]![frmOrdersCrit2]![txtEndDate]
GROUP BY tblCustomers.CustomerName, tblOrderDetails.CropOrdered,
tblPacks.PackName, tblOrders.CustomerID;

I also use a command button that has the criteria string to filter the
report. Therefore, I want to build all criteria as a string on the
command button click event and avoid having another one just doing the
OrderDate explicitly on the query design ie to avoid using:
Between [Forms]![frmOrdersCrit2]![txtStartDate] And [Forms]!
[frmOrdersCrit2]![txtEndDate]
in the query design.

If it works... which it should... why do you want to avoid it!?


John W. Vinson [MVP]

 

[niuginikiwi]

Hi John,
I have decided to go the way you suggested. It works okay for me at
the moment. I used VBA to control the supplying of criteria on the
criteria form via the command button to open that report.
I am happy to leave it at that for now.
Thanks for the advice.
Very much appreciated.

Thanks,
niuginikiwi