have a crosstab query that works ok so far
Need a dynamic date criteria. I use a tempVar from a Form. If I write in vba
TempVars!SelectedReportDate = "05/06/2020" this is ok
TempVars!SelectedReportDate = "Between 8/05/2020 and 08/06/2020"
this does not work. when I requery the subform I get error 3420 Object invalid or no longer set and the subform gives me 1 records with all the 9 fields shown as #Name?
Tried many combinations with apostrophe but could not get it to work. the two date will come eventually form 2 textboxes on the form. Any ideas? thanks
Expand|Select|Wrap|Line Numbers
- TRANSFORM CDbl(Nz(Sum([PaymentAmount]),0)) AS AmountTotal
- SELECT tblInvoices.DateProcessed AS RepDate, Count(tblPayments.PaymentID) AS CountOfPaymentID, Sum([AmountTotal]) AS GrandTotal
- FROM tblInvoices INNER JOIN (tblPaymentType INNER JOIN tblPayments ON tblPaymentType.PaymentTypeID = tblPayments.fkPaymentTypeID) ON tblInvoices.InvoiceID = tblPayments.fkInvoiceID
- WHERE (((tblInvoices.DateProcessed)=[TempVars]![SelectedReportDate]))
- GROUP BY tblInvoices.DateProcessed
- PIVOT tblPaymentType.PaymentType In ("Card","Cash","Other","Voucher","Discount","Refund");