Help with Form searching subform query criteria

[Garindan]

Hi all, I have a form frmProductSalesCalculator, and a subform fsubProductSalesCalculatorDetails. The subform record source is qselProductSalesCalculator.

I'm trying to set up the form to search/pass criteria to the subform query.

qselProductSalesCalculator:-
Salesperson
Product
ProductWeight
Quantity
QuantityxWeight
DeliveryDate
DeliveryTime

I am wanting to search by Salesperson, between Delivery Dates, and/or Delivery Time (which is AM or PM only).

However, I want to not show Delivery Date or Time, but search by it. And then group by Salesperson and Sum on Quantity and QuantityxWeight.

So hopefully what I am left with is for a salesperson - how many total of each product they have sold, and how much that weighs, between a date range.

I would like saleperson to be able to be left blank and display everything, including entries without a salesperson. The same for Delivery Time, so they can choose whether to filter by delivery time (am/pm) or not.

So basically the main search is between a date range, then filtered by salesperson/delivery time or not.

I can't get the query right. I have it displaying all entries for salesperson with nothing entered in the search, or a salesperson plus all blank entries!

And when I try to set up the date search, I just get an error message saying the expression is too complicated. Here's what I have so far -

SELECT qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity, Sum(qselProductSalesCalculator.QuantityxWeight) AS SumOfQuantityxWeight
FROM qselProductSalesCalculator
WHERE (((qselProductSalesCalculator.DeliveryTime) Like [Forms]![frmProductSalesCalculator].[cmbDeliveryTime] & "*") AND ((qselProductSalesCalculator.DeliveryDate) Between [Forms]![frmProductSalesCalculator].[txtStartDate] And [Forms]![frmProductSalesCalculator].[txtEndDate]))
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product
HAVING (((qselProductSalesCalculator.Salesperson) Like [Forms]![frmProductSalesCalculator].[cmbSalesperson] & "*" Or (qselProductSalesCalculator.Salesperson) Is Null));

Thanks a lot for any help!

 

[Garindan]

Any idea's? Struggling with this