Access Cross tab query bug when referencing forms?

Question

There seems to be a bug with crosstab queries such as not being able to reference form fields as criteria in the crosstab or queries supplying data to the crosstab query. For example: Entering a date directly as criteria will work but not when the date is referenced to an open form. This annoyance has been around since Access 2003 or earlier.

Is there a workaround available or will Microsoft fix this as an update?

Thank You

Kurt

Answer 1

Yes, that will work in A2003.  Parameters have probably been part of Jet SQL since day 1 (at least A2 when I started using it).  There are occasional situations where declaring parameters in simple SELECT queries is necessary to specify the data type because Access' automatic type conversion guesses differently than you intended.

As an aside, QueryDef objects have a Parameters collection that you can use to see the name of each parameters and to set its value when using VBA code to retrieve the query's records.

Answer 2

Marshall,

If you use Parameters it prompts a pop-up and I don't want that. I want the query to quietly use an open form as reference. No being able to do that really limits the use of crosstab queries which are such powerful tools to extract data.

Kurt

Answer 3

I'm not sure you can call it a bug just because Access diesn't take care of it automatically.  The "correct" way to use parameters in a query is always declare them, but then Access does seem to deal with missing Parameter declarations in other kinds of queries.

You can find the details about declaring query parameters by searching Help for the topic:

   PARAMETERS Declaration

Sorry, I just couldn't resist posting a long winded RTFM  ;-)

Answer 4

Thanks Daniel,

Could you be more specific with an example? How do you redefine SQL to accept form value?

Kurt Boettcher

Answer 5

As you mentioned this has been an issue for quite some time now and for whatever the reason MS has never addressed it.

That said the work around that I use, and that was taught to me, was to have to redefine the query to that the criteria is explicitly specified.  So, at runtime, you need to redefine your query's SQL to specify the criteria based on your form values, and then run it to produce your crosstab...

Daniel Pineault

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net