Programming Microsoft Access Visual Basic
Apparent Microsoft Access Bug with Global Variables &
OutputTo Using Crosstab Query Docmd Method
We have recently encountered what appears to be a bug in
Microsoft Access 2000, 2002 & 2003. This bug occurs when we setup a query
with global variables in the criteria (where clause). Then we use this
query as input to a crosstab query. The next step is to use the
Docmd.OutputTo command to send the results of the cross tab out as an Excel
spreadsheet. The bug is centered around the OutputTo method.
The easiest work around we found was to change the crosstab
query to a make table query and then export the temporary table to Excel - this
method works fine.
There are other bugs 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. (thanks Bill).
Try our downloadable Access database demonstration of using
Global variables as query parameters.