Solved Calculate total sale amount of a product in a time period (1 Viewer)

[Babycat]

Hi every,
I have tried to search if any similiar post, but I did not found what i need...

I have two tables as per attached picture, on the mainform there are 2 text boxes FromDate and ToDate to specify the exactly time period.
How to calcuate subtotal sale amount of a product between [FromDate; ToDate].
I dont know how to group by, can any one please help?

SELECT TBLORDERDETAIL.ProductID, TBLORDER.CreatedDate, Sum(([Qty]*[Cost])) AS ExtPrice
FROM TBLORDER INNER JOIN TBLORDERDETAIL ON TBLORDER.OrderID = TBLORDERDETAIL.OrderID
GROUP BY TBLORDERDETAIL.ProductID, TBLORDER.CreatedDate;

 

[pbaldy]

Right click in the criteria box under the date field and select build. Create a criteria that refers to the 2 textboxes with Between:

Between Forms!... And Forms!...

I suspect you'll want to change Group By to Where on the date field.

 

[Babycat]

Yes, if I let CreatedDate in select query, I have doubt I can group them up. It's actually not working because there orders are on different dates.

I thought about a subquery in ProductID criteria, it might be:

SELECT DISTINCT O.ProductID
FROM TBLORDER AS O INNER JOIN TBLORDERDETAIL AS OD ON O.OrderID = OD.OrderID
WHERE (O.CreatedDate between Froms!..!FromDate AND Froms!..!ToDate);

Is that what you meant, Pbaldy?

Another requirement, with the date textbox on form main, I would like my form behaves as following:
+ if FromDate and ToDate are both null, then all of that products's sale records are sum up.
+ if FromDate = Null, ToDate = a date: It sums up from the past to ToDate
+ if FromDate = a date, ToDate = Null : It sums up from FromDate toward
+ if FromDate = a date, ToDate = another date : It sums up from FromDate to ToDate

What could be easy and effective SQL strings?

My current SQL string is as below which is too long...

SELECT DISTINCT OD.ProductID
FROM TBLORDER AS O INNER JOIN TBLORDERDETAIL AS OD ON O.OrderID = OD.OrderID
WHERE     (
        (
            (O.CreatedDate>=[Forms]![FrmInvCheck]![Txt_F_FromDate])
                AND
            (O.CreatedDate<[Forms]![FrmInvCheck]![Txt_F_FromDate]+1)
        )
        OR
        (
            ([Forms]![FrmInvCheck]![Txt_F_FromDate] & ""="")
                AND
            (O.CreatedDate<[Forms]![FrmInvCheck]![Txt_F_ToDate]+1)
        )
        OR
        (
            (O.CreatedDate>=[Forms]![FrmInvCheck]![Txt_F_FromDate])
                AND
            ([Forms]![FrmInvCheck]![Txt_F_ToDate] & ""="")
        )
        OR
        (
            ([Forms]![FrmInvCheck]![Txt_F_FromDate] & ""="")
                AND
            ([Forms]![FrmInvCheck]![Txt_F_ToDate] & ""="")
        )
    );

 

[plog]

You just need 2 WHERE clauses--and in each you just need to process the data from the form and substitute a date that makes it work if null:


Table.FromDate>=If(InputForm!FromDate NULL Then 1/1/1900 else InputForm!FromDate)

So, that's not real code, but it gives you the idea. For the To date, instead of 1/1/1900 use Date()+1

 

[Babycat]

You just need 2 WHERE clauses--and in each you just need to process the data from the form and substitute a date that makes it work if null:


Table.FromDate>=If(InputForm!FromDate NULL Then 1/1/1900 else InputForm!FromDate)

So, that's not real code, but it gives you the idea. For the To date, instead of 1/1/1900 use Date()+1

Thank you.

Following code is working as expected.

SELECT DISTINCT OD.ProductID, O.CreatedDate, O.CreatedDate
FROM TBLORDER AS O INNER JOIN TBLORDERDETAIL AS OD ON O.OrderID = OD.OrderID
WHERE (
    ( O.CreatedDate>= IIf([Forms]![FrmInvCheck]![Txt_F_FromDate] & ""="",#1/1/1990#,[Forms]![FrmInvCheck]![Txt_F_FromDate]))
 
        AND

    (O.CreatedDate<IIf([Forms]![FrmInvCheck]![Txt_F_ToDate] & ""="",Date()+1,[Forms]![FrmInvCheck]![Txt_F_ToDate]+1))
    
    );