How to filter a crosstab query?.

[ebs17]

Variant 3: Use of a parameter table

SELECT D.*
FROM tblData AS D, ptblParameters AS P
WHERE D.Anydate = P.parDate

The parameter table is introduced via CROSS JOIN. However, since it usually only has one record, no unwanted data duplication takes place.
You just have to reset the value in the table before calling the query.
Advantage here too: This can also be used in deeper query levels.

 

[ebs17]

Variant 4. Dynamic SQL (composing the SQL string using VBA)

sSQL = "TRANSFORM ..." & _
   " WHERE P.PDATE = " & Format(Forms.AnyForm.txtAnyDate, "\#yyyy\-mm\-dd\#") & _
   " ..."

Variant 5: Replacing a wildcard from the template query

csSQL = "TRANSFORM ..." & _
   " WHERE P.PDATE = [$1Date]" & _
   " ..."
sSQL = Replace(csSQL, "$1Date", Format(Forms.AnyForm.txtAnyDate, "\#yyyy\-mm\-dd\#"))

 

[ebs17]

Form references and TempVar have problems as parameter passers when they are used at a deeper level (subquery). I don't know whether this changes through the parameter listing. I don't check something like that, as I said, for style reasons.

 

[ebs17]

TempVars are only available with Acc2007. Otherwise nothing has changed regarding queries in Access since Acc2000, so I see no reason for a version dependency.
Did you notice the word subquery?

 

[Gasman]

TempVars are only available with Acc2007

Surely that is 'since' 2007 ?

 

[ebs17]

'since' 2007 ?

Correct. Someone is reading along, I'm thrilled.

 

[moi]

I inserted the SQL string from one query into the crosstab query. Passing parameters to ONE object is clearer.

To pass parameters, I have chosen a public function as a variant. This function takes the (validated) Date value from a form text field.

Public Function fctparDate() As Date
   fctparDate = Forms.AnyForm.txtAnyDate
End Function

TRANSFORM
   SUM(Q.AMOUNTPAID) AS SumOfAMOUNTPAID
SELECT
   Q.BUYERID,
   B.byrFName,
   B.byrLName,
   B.propID,
   B.propBlk,
   B.propLot,
   B.propSQM,
   Q.PDATE,
   Q.ARNO,
   Q.BankName,
   Q.AccNo,
   Q.AccName,
   SUM(Q.AMOUNTPAID) AS [Total Of AMOUNTPAID]
FROM
   tblBuyer AS B
      INNER JOIN
         (
            SELECT
               P.PAYID,
               P.BUYERID,
               P.PDATE,
               P.ARNO,
               P.AMOUNTPAID,
               P.MOPID,
               M.MOP,
               M.BankName,
               M.AccNo,
               M.AccName,
               P.REFTRANSNMBR,
               P.REMARKS
            FROM
               tblMOP AS M
                  INNER JOIN tblPayment AS P
                  ON M.MopID = P.MOPID
            WHERE
               P.PDATE = fctparDate()
         ) AS Q
         ON B.byrID = Q.BUYERID
GROUP BY
   Q.BUYERID,
   B.byrFName,
   B.byrLName,
   B.propID,
   B.propBlk,
   B.propLot,
   B.propSQM,
   Q.PDATE,
   Q.ARNO,
   Q.BankName,
   Q.AccNo,
   Q.AccName
PIVOT
   Q.MOPID

There are at least three other variants.

Hi ebs17,

Thank you for helping me to my query and crosstab.. I pasted your sql statement into the sql design view of my crosstab, then I copied your fctparDate function into module, and save as "fctparDate"..but when open/run my crosstab, I got the attached msg..

Can you please guide me on how to use your sql and function.. I am new to advance queries/crosstab pls need more help.

Thank you.

 

[moi]

So where in that sql have you assigned the parameter - see post #5

I would have expected to see something like

PARAMETERS [Enter Date] Date;
SELECT tblPayment.PAYID, tblPayment.BUYERID, tblPayment.PDATE, tblPayment.ARNO, tblPayment.AMOUNTPAID, tblPayment.MOPID, tblMOP.MOP,
...
...

Hi CJ_London,

Can you please expand the above, I am new to advance queries/crosstab, I can do a simple query as of now, please need more help..

Thank you.

 

[June7]

Did you read Allen Browne article linked in post 21?

Exactly what do you not understand?

Build crosstab query - use the build wizard if that helps. Modify design - open Parameters dialog with button on ribbon.

 

[moi]

Did you read Allen Browne article linked in post 21?

Exactly what do you not understand?

Build crosstab query - use the build wizard if that helps. Modify design - open Parameters dialog with button on ribbon.

Yes.. i did read it.. the problem i am struggling to get work is the date range filter..

 

[June7]

So you tried Parameters? Exactly what issue did you encounter - error message, wrong result, nothing happens? Post your attempted SQL as text, not image. Are you outside U.S.A. using non-US date?

 

[Gasman]

Hi ebs17,

Thank you for helping me to my query and crosstab.. I pasted your sql statement into the sql design view of my crosstab, then I copied your fctparDate function into module, and save as "fctparDate"..but when open/run my crosstab, I got the attached msg..

Can you please guide me on how to use your sql and function.. I am new to advance queries/crosstab pls need more help.

Thank you.

You do not name modules the same as sub or function names.

 

[ebs17]

I copied your fctparDate function into module

It must be a standard module, not a form's module. And of course the form being accessed must also be open at this moment and contain a valid value.
Additionally, @Gasman is right again.

 

[CJ_London]

Can you please expand the above,

not sure how much easier it could be - in the query window on the ribbon

Here there are two parameters specified - the first provides a prompt for the user to enter a date, the second to a control on a form (which as others have said must be open at the time the query is run - and populated).

This will duly add the required code to the sql

PARAMETERS [Enter Date] DateTime, [ [forms]]![myform]![mycontrol] DateTime;
SELECT .......


Note this is greyed out in the SQL window but you can of course add the line if required.

 

[June7]

Advise not to use popup input parameters as cannot validate input. Refer to control on form.