Crosstab query with criteria form (1 Viewer)

I

Iceman95

Registered User.

Local time

Today, 03:16

Joined

Dec 7, 2004

Messages

14

• Dec 7, 2004

• #1

Hi,

I have a crosstab query based on a select query requiring parameters to be entered via a dialog form.

Everything works fine when the parameters are typed directly via a dialog box (select query and crosstab query run perfectly).

When using the form to enter the paramaters, only the select query runs correctly.

It seems that a crosstab query cannot simply work with parameters entered through a form.

Of course, I've declared all parameters in the crosstab query (again, when parameters are set directly, the Xtab query runs without problem).

So, can anyone confirm that there is a bug concerning this particular topic (running a crosstab query via criteria form) ? Or am I doing something wrong ?

Thanks in advance for any help (and please apologize my -well, not so good- English syntax !)

Tom

 

J

jason_lee

New member

Local time

Today, 11:16

Joined

Dec 4, 2004

Messages

8

• Dec 7, 2004

• #2

Hi,Tommy

By I know, this is not your wrong, crosstab query doesn't support the parameters (except viaing the dialog form you said), it's a limit of Access.

 

maxmangion

AWF VIP

Local time

Today, 02:16

Joined

Feb 26, 2003

Messages

2,805

• Dec 7, 2004

• #3

in your query window, did you to the Query -> Parameters Menu and set declare the parameters you're using there ?

 

I

Iceman95

Registered User.

Local time

Today, 03:16

Joined

Dec 7, 2004

Messages

14

• Dec 7, 2004

• #4

Yes, I've declared all parameters ; the problem does not lie in the query itself, that works perfectly. As Jason said in his post, I'm afraid this a bug in Access.

The only thing that surprises me is that Microsoft Knowledge Base doesn't reference it.

 

R

Rich

Guest

• Dec 7, 2004

• #5

Crosstab queries do work with parameters entered through a form, I'm not sure where you gained the information that they don't.
You have not entered the correct references to the form and text box(s).
Use the code builder to get the correct syntax

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 18:16

Joined

Aug 30, 2003

Messages

36,125

• Dec 7, 2004

• #6

I agree with Rich. I have a bunch of crosstabs getting their criteria from forms. However, unlike most queries, crosstabs require the parameters to be explicitly declared, so that is certainly the most likely source of your trouble.

 

I

Iceman95

Registered User.

Local time

Today, 03:16

Joined

Dec 7, 2004

Messages

14

• Dec 8, 2004

• #7

No, as stated before, all parameters declarations have been done correctly. Once again, when the query is launched without the form, the parameters are prompted via dialog boxes ; should they hadn't been declared, Access would have given an error message.

Furthermore, the query does not open with no record, it does not open at all (double click on the query in the database window has no effect, while it should at least open the query in view mode, or give an error message).

This leads me to the idea that there's some bug with that kind of query/form interaction.

I encountered the same issue in several forums where users reported the same limitation.

So, any other idea ?

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 18:16

Joined

Aug 30, 2003

Messages

36,125

• Dec 8, 2004

• #8

Curious; as I said, I have a number of crosstab queries/reports in production applications that get their criteria from user input forms. Can you post a sample db we can play with?

 

R

Rich

Guest

• Dec 9, 2004

• #9

Post the SQL of your query

 

I

Iceman95

Registered User.

Local time

Today, 03:16

Joined

Dec 7, 2004

Messages

14

• Dec 9, 2004

• #10

Here is the SQL of my query :

PARAMETERS [Forms]![Dialogue]![DateDébut] DateTime, [Forms]![Dialogue]![DateFin] DateTime, [Forms]![Dialogue]![NomLiquidateur] Text, [Forms]![Dialogue]![Option] Bit;
TRANSFORM Sum([Requête union anomalies Requête (liquidateur) (2ème niveau)].SommeDeSommeDeCompteDeAnomalie) AS [La valeur]
SELECT [Requête union anomalies Requête (liquidateur) (2ème niveau)].Prestation
FROM [Requête union anomalies Requête (liquidateur) (2ème niveau)]
GROUP BY [Requête union anomalies Requête (liquidateur) (2ème niveau)].Prestation
PIVOT [Requête union anomalies Requête (liquidateur) (2ème niveau)].Anomalie;
(sorry, I use the French A97 version ! But I think this remains quite understandable for non French-speaking readers)

Requête union anomalies Requête (liquidateur) (2ème niveau) is the select query my crosstab query is based on.
Dialogue is the form used to enter parameters.

After several tests, I wonder if the trouble does not come from a Null value problem : i.e the [NomLiquidateur] criterion is set in my select query as [Forms]![Dialogue]![NomLiquidateur] OR LIKE [Forms]![Dialogue]![NomLiquidateur] IS NULL, so as to get all records when the criterion is left blank.

It seems that a crosstab query has some problems dealing with that, when the criteria are entered via a form (no problem when entered via a dialog box).

Is that ringing a bell to anyone ? Look at http://www.access-programmers.co.uk/forums/showthread.php?t=14040&highlight=crosstab+query+null

 

Last edited: Dec 9, 2004

X

XelaIrodavlas

Registered User.

Local time

Today, 02:16

Joined

Oct 26, 2012

Messages

174

• Jul 16, 2014

• #11

Hi all,

I don't know if you ever solved this but I have had the same problem and just spent two hours on Le Goog trying to figure it out... So for the sake of anyone else who finds this thread, here was the painfully simple answer which worked for me...

Set the parameters in the Select query as well as the Crosstab query.

Can't guarantee this will work for everyone of course, but it was certainly the part I was missing...

for reference here is the SQL that works for me, I was trying to count number of health and safety incidents by Classification (columns) vs Site (Rows) within a date range specified on the form [ReportsFrm]:

Code:Copy to clipboard

PARAMETERS [Forms]![ReportsFrm]![SiteID] Short, [Forms]![ReportsFrm]![DateFrom] DateTime, [Forms]![ReportsFrm]![DateTo] DateTime;

TRANSFORM Count(SubQuery1.CountOfEventID) AS CountOfCountOfEventID

SELECT SubQuery1.SiteID, Count(SubQuery1.CountOfEventID) AS [Total Of CountOfEventID]

FROM (
PARAMETERS [Forms]![ReportsFrm]![SiteID] Short, [Forms]![ReportsFrm]![DateTo] DateTime, [Forms]![ReportsFrm]![DateFrom] DateTime; 
SELECT EventReports.Classification, Count(EventReports.EventID) AS CountOfEventID, EventReports.SiteID 
FROM EventReports 
GROUP BY EventReports.Classification, EventReports.SiteID, EventReports.HazNerDate 
HAVING (((EventReports.HazNerDate)>=[Forms]![ReportsFrm]![DateFrom] And (EventReports.HazNerDate)<=[Forms]![ReportsFrm]![DateTo]))
)  AS SubQuery1

GROUP BY SubQuery1.SiteID

PIVOT SubQuery1.Classification In ("Near Hit","Hazard Observation","First Aid","Medical Treatment - RTW","Medical Treatment - Over 1 Day","Lost Time Accident - Over 7 Days","Fatality","Damaged Plant/Equipment","Environmental","Observation of Good Practice");

Hope this helps,

 

R

Reese

Registered User.

Local time

Yesterday, 21:16

Joined

Jan 13, 2013

Messages

387

• Sep 1, 2014

• #12

Thank you AlexSalvadori! I had this same problem and your solution works great.

 

V

vbaInet

AWF VIP

Local time

Today, 02:16

Joined

Jan 22, 2010

Messages

26,374

• Sep 1, 2014

• #13

For completeness, here's how to set parameters in the Query Designer user interface:

http://www.fmsinc.com/tpapers/queries/#Parameters

 

A

ahmed.hafez

New member

Local time

Today, 04:16

Joined

Jan 30, 2018

Messages

1

• Jan 30, 2018

• #14

to use in criteria form object you have to declare it in
Design tab ---> parameter ----> show/hide Group -----> Parameters
you will find 2 columns
write in the first column your object you want in criteria like this [forms]![class]![id]
in the second column the data type like text or interger .

after this if you use this [forms]![class]![id] in criteria will work normal