How to filter a crosstab query?.

[moi]

Hello all,

I am strugling to make a filter to my crosstab query, but i always get a "MS Access database engine does not recognize [Enter Remarks]as a valid field name or expression..

I think crosstab query is not same as ordinary query that can easily filter..

Can someone pls help me, i realy need this done pls..

Thank you.

 

[Gasman]

I would have thought you would filter the data before the crosstab?

 

[moi]

I would have thought you would filter the data before the crosstab?

Hi Gasman, can you pls help me.. I need to filter by date, and presented to report.

 

[Gasman]

Filter by the date then?
I off out to pick up a passenger, and besides without any data/sql/code I could not do anything anyway.

The Open Report comand has a Where parameter?
Try researching the syntax. As I have told you before Google is a great resource. If you do not learn to help yourself, this will always be a huge task.

 

[CJ_London]

with crosstabs, your need to specify the parameters and their data types - in query design view, click on the parameters tab in the ribbon

 

[Pat Hartman]

I am strugling to make a filter to my crosstab query, but i always get a "MS Access database engine does not recognize [Enter Remarks]as a valid field name or expression..

Usually, you filter the input to the crosstab rather than the crosstab. However, as @CJ_London mentioned, the CrossTab REQUIRES that you specifically define any parameters. That includes those from child queries, not just the CrossTab query itself. If the CrossTab is based on a query rather than a table and that query has parameters, the parameters MUST also be defined in the CrossTab.

This makes CrossTab queries unique since none of the other types require parameters to be specifically defined. Apparently that developer didn't get the memo or maybe he did but the others didn't

 

[moi]

Usually, you filter the input to the crosstab rather than the crosstab. However, as @CJ_London mentioned, the CrossTab REQUIRES that you specifically define any parameters. That includes those from child queries, not just the CrossTab query itself. If the CrossTab is based on a query rather than a table and that query has parameters, the parameters MUST also be defined in the CrossTab.

This makes CrossTab queries unique since none of the other types require parameters to be specifically defined. Apparently that developer didn't get the memo or maybe he did but the others didn't

Hi pat,

Thanks, i will try to put the parameters before the crosstab then..

Thank you pat.

 

[CJ_London]

If the CrossTab is based on a query rather than a table and that query has parameters, the parameters MUST also be defined in the CrossTab

If you have declared the parameters in the query they do not need to be restated in the crosstab. If the parameters are not declared in the query they must be declared in the crosstab. I.e. you do not need to declare them twice

 

[moi]

If you have declared the parameters in the query they do not need to be restated in the crosstab. If the parameters are not declared in the query they must be declared in the crosstab. I.e. you do not need to declare them twice

Hi CJ_London,

I have tried to filter the qry that feed the crosstab, but i always got the attached msg..

What I did is, I put "[Enter Date]" on the criteria row of my [PDate] field on my query that feed my crosstab, I don't if this is the correct way to filter the query..can you please help me.. If i open the filtered query it gives me what i want, then when I put that query in the crosstab it pops the attached msg.

Thank you

 

[ebs17]

When people talk about queries, here a crosstab query, for days - wouldn't it be helpful information to show the SQL statement here as text/code?
QueryDef means that a lot of things are defined and can be read.

Besides, I would repeat: There are different ways to pass parameters to queries. A more extensive toolbox allows for more flexibility.

 

[moi]

Usually, you filter the input to the crosstab rather than the crosstab. However, as @CJ_London mentioned, the CrossTab REQUIRES that you specifically define any parameters. That includes those from child queries, not just the CrossTab query itself. If the CrossTab is based on a query rather than a table and that query has parameters, the parameters MUST also be defined in the CrossTab.

This makes CrossTab queries unique since none of the other types require parameters to be specifically defined. Apparently that developer didn't get the memo or maybe he did but the others didn't

Hi Pat,

Can you please examine the sql view of my crosstab, i tried the put the parameter from the query that feeds the crosstab, still i could no get to work..

TRANSFORM Sum(qry_Payment_MOP_CT.AMOUNTPAID) AS SumOfAMOUNTPAID
SELECT qry_Payment_MOP_CT.BUYERID, tblBuyer.byrFName, tblBuyer.byrLName, tblBuyer.propID, tblBuyer.propBlk, tblBuyer.propLot, tblBuyer.propSQM, qry_Payment_MOP_CT.PDATE, qry_Payment_MOP_CT.ARNO, qry_Payment_MOP_CT.BankName, qry_Payment_MOP_CT.AccNo, qry_Payment_MOP_CT.AccName, Sum(qry_Payment_MOP_CT.AMOUNTPAID) AS [Total Of AMOUNTPAID]
FROM tblBuyer INNER JOIN qry_Payment_MOP_CT ON tblBuyer.byrID = qry_Payment_MOP_CT.BUYERID
GROUP BY qry_Payment_MOP_CT.BUYERID, tblBuyer.byrFName, tblBuyer.byrLName, tblBuyer.propID, tblBuyer.propBlk, tblBuyer.propLot, tblBuyer.propSQM, qry_Payment_MOP_CT.PDATE, qry_Payment_MOP_CT.ARNO, qry_Payment_MOP_CT.BankName, qry_Payment_MOP_CT.AccNo, qry_Payment_MOP_CT.AccName
PIVOT qry_Payment_MOP_CT.MOPID;

with my basic knowledge, i just could not get it to work..

Many thanks..

 

[CJ_London]

I have tried to filter the qry that feed the crosstab, but i always got the attached msg..

show the sql to your query. It is the only way you will get any help

 

[moi]

show the sql to your query. It is the only way you will get any help

Posted already..

 

[CJ_London]

that's for your crosstab - need to see the sql to your qry_Payment query

 

[Gasman]

Might want to start looking at aliases if your names are going to be that long?

 

[moi]

that's for your crosstab - need to see the sql to your qry_Payment query

Hi CJ_London,

Here is the sql view for qry_payment.

SELECT tblPayment.PAYID, tblPayment.BUYERID, tblPayment.PDATE, tblPayment.ARNO, tblPayment.AMOUNTPAID, tblPayment.MOPID, tblMOP.MOP, tblMOP.BankName, tblMOP.AccNo, tblMOP.AccName, tblPayment.REFTRANSNMBR, tblPayment.REMARKS
FROM tblMOP INNER JOIN tblPayment ON tblMOP.MopID = tblPayment.MOPID
WHERE (((tblPayment.PDATE)=[Enter Date]))
ORDER BY tblPayment.BUYERID;

Thank you..

 

[CJ_London]

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,
...
...

 

[ebs17]

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.

 

[CJ_London]

@ebs17 - think you may be missing the point,. With a crosstab, you must declare the parameter and its data type in a parameter section of the query

PARAMETERS [Enter Date] Date;

So in your query you would need to have

PARAMETERS fctparDate() Date;

Although perhaps your solution will work without the declaration since the function is declared as a date datatype. Up to the OP to test. I'm not in a position to test right now

 

[ebs17]

The function is simply executed like Left, Nz, etc. The fact that it involves passing a parameter is not important.
Eval(Forms.AnyForm,txtAnyDate) should also work, but I don't like form references in queries.

Regarding parameter listing as you show: I like to use this, but then the parameter has to be PASSED to the query (parX = #3/4/2024#). Collecting the value yourself via form reference or function or from a TempVar contradicts the approach of the defined interface to the object query, at least in my sense of style.
For convenience you need VBA, also to transfer the parameters. The query can then only be received as a recordset, which raises the question of how it can be used further. You can't assign and use a recordset as a RecordSource everywhere.