Crosstab Query

[Chris]

All,

I have a form (frmFrontEnd) with a combobox (location) the
address of which is [forms]![FrmFrontEnd]![location].

I aslo have a cross tab query which I want to filter to
results for just the location specified in the combobox.

I have used the Expression builder to put the criteria of
[forms]![FrmFrontEnd]![location] into the location field
for the crosstab query. When I run the query Access tells
me that it does not recognise [forms]![FrmFrontEnd]!
[location] as a valid filed name or expression. I don't
get this error when I use the combobox as a filter on
standard queries!

What is going wrong, or how else can I use the value in
the combobox to act as a filter on a crosstab query?

TIA,

Chris

 

[Duane Hookom]

Crosstabs require you to explicitly datatype your parameters. Select
Query|Parameters and enter:
[forms]![FrmFrontEnd]![location] Text (or other data type)

 

[Chris]

Thanks Duane,

I would have never have spotted that. You've just saved
me hours of time, thanks


Chris

-----Original Message-----
Crosstabs require you to explicitly datatype your parameters. Select
Query|Parameters and enter:
[forms]![FrmFrontEnd]![location] Text (or other data type)

--
Duane Hookom
MS Access MVP

All,

I have a form (frmFrontEnd) with a combobox (location) the
address of which is [forms]![FrmFrontEnd]![location].

I aslo have a cross tab query which I want to filter to
results for just the location specified in the combobox.

I have used the Expression builder to put the criteria of
[forms]![FrmFrontEnd]![location] into the location field
for the crosstab query. When I run the query Access tells
me that it does not recognise [forms]![FrmFrontEnd]!
[location] as a valid filed name or expression. I don't
get this error when I use the combobox as a filter on
standard queries!

What is going wrong, or how else can I use the value in
the combobox to act as a filter on a crosstab query?

TIA,

Chris

.

 

[Guest]

I, too, have a similar problem. My crosstab query's recordsource is another simple query. Both queries are the basis for reports where I want to filter the info by month. I built a form where the user can select the month (unbound combo box) and then I use this value to select all the dates that match the given month. This works fine for my simple query, but I got the error, described below. I tried fixing the crosstab query's filter so that under the Date column of the crosstab query, I put the following condition: Month([Date])=[Forms]![frm_SelectReport]![MonthNo] Tex
but I get the error: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

Any ideas on how to get this thing to work?

Thanks
Carri

----- Duane Hookom wrote: ----

Crosstabs require you to explicitly datatype your parameters. Selec
Query|Parameters and enter
[forms]![FrmFrontEnd]![location] Text (or other data type

--
Duane Hooko
MS Access MV

All

I have a form (frmFrontEnd) with a combobox (location) th

address of which is [forms]![FrmFrontEnd]![location]

I aslo have a cross tab query which I want to filter t

results for just the location specified in the combobox

I have used the Expression builder to put the criteria o

[forms]![FrmFrontEnd]![location] into the location fiel
for the crosstab query. When I run the query Access tell
me that it does not recognise [forms]![FrmFrontEnd]
[location] as a valid filed name or expression. I don'
get this error when I use the combobox as a filter o
standard queries

What is going wrong, or how else can I use the value i

the combobox to act as a filter on a crosstab query

TIA
Chri

 

[John Spencer (MVP)]

1) You need to do this at the beginning of the query, not in the where clause.
2) Since you are getting a Month Number, you need to declare the parameter as a
number type.

In Access 2K, that would probably look something like.

PARAMETERS [Forms]![frm_SelectReport]![MonthNo] Short;
<REST OF YOUR EXISTING Query

If you are using the query grid, open the query in design view and select Query:
Parameters... from the menu.

In the resulting dialog, enter [Forms]![frm_SelectReport]![MonthNo] in the first
parameter cell and then select Single from the datatype dropdown.

I, too, have a similar problem. My crosstab query's recordsource is another simple query. Both queries are the basis for reports where I want to filter the info by month. I built a form where the user can select the month (unbound combo box) and then I use this value to select all the dates that match the given month. This works fine for my simple query, but I got the error, described below. I tried fixing the crosstab query's filter so that under the Date column of the crosstab query, I put the following condition: Month([Date])=[Forms]![frm_SelectReport]![MonthNo] Text
but I get the error: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

Any ideas on how to get this thing to work??

Thanks,
Carrie

----- Duane Hookom wrote: -----

Crosstabs require you to explicitly datatype your parameters. Select
Query|Parameters and enter:
[forms]![FrmFrontEnd]![location] Text (or other data type)

--
Duane Hookom
MS Access MVP

All,

I have a form (frmFrontEnd) with a combobox (location) the

address of which is [forms]![FrmFrontEnd]![location].

I aslo have a cross tab query which I want to filter to

results for just the location specified in the combobox.

I have used the Expression builder to put the criteria of

[forms]![FrmFrontEnd]![location] into the location field
for the crosstab query. When I run the query Access tells
me that it does not recognise [forms]![FrmFrontEnd]!
[location] as a valid filed name or expression. I don't
get this error when I use the combobox as a filter on
standard queries!

What is going wrong, or how else can I use the value in

the combobox to act as a filter on a crosstab query?

TIA,
Chris

 

[Guest]

Thank you for the reply. I did as you suggested, even substituting Integer for Single. The results are working well - even filtering the records using the parameter from the reports dialog form. However, when I go to view (print preview) the report, I get nothing, even though the crosstab query is displays results when manually run.

Is there something I am doing wrong in the reports dialog form that uses a macro to open the report? The report was displaying the crosstab query records before I started using the reports dialog form.

Thanks
Carrie

 

[John Spencer (MVP)]

I have no idea. I don't recall seeing your original SQL and I don't know how
your report is structured.

Do you get any error messages?

 

[Duane Hookom]

There are a couple sample mdbs in the crosstab reports demo at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP

My report has 50 labels and textboxes. Depending on the number of columns

(which will vary each month), the report will hide the unused boxes and then
distribute the others evenly across the width of the page.

Hopefully this helps. I'm hitting a wall as to what the problem is here.

Thanks for taking the time to help.