How to Handle Crosstabs and Filters

  • Thread starter Thread starter DOYLE60
  • Start date Start date
D

DOYLE60

I am trying to do a crosstab query based on another query which has a filter on
it. The filter was the following:

Between [Forms]![DialogPrintfrm]![DateLow]+1 And
[Forms]![DialogPrintfrm]![DateHigh]

When trying to run the crosstab, the database gave me an error: "The Microsoft
Jet Database engine does not recongize "[Forms]![DialogPrintfrm]![DateLow]" as
a valid field name or expression."

So I put in the expression in the parameters along with the DateHigh. And I
ran the crosstab. It worked. I then went back to the query the crosstab is
based on and added more filters. Ones like this:

Like
(IIf(IsNull([Forms]![DialogPrintfrm]![MatchChosen]),"*","|[Forms]![DialogP
rintfrm]![MatchChosen]|"))

And it worked without giving me parameter problems. Surprising but true! I
put in four other filters like the one above and checked them all out. They
worked.

But I went back to the database a few days later and for some reason the
crosstab didn't work. It gave me the old message again about not recognizing
the filters. The subsequent ones. So after I put the troublesome filter in
the parameters, it would give me the next one it had issues with, then the
next, and the next.... I put them all in one at a time, and when I ran it, it
gave me nothing.

So why does it work one day and not the next? How does one work with filters
in Crosstabs. I just don't get it. In fooling around with them, trying to
solve this issue, all sorts of wierd things are happening.

The only way I see around it is to build a Make Table query between the two
queries; have the query with filters, make a table, and then the crosstab based
on the table. That would negate the filters.

Any help and suggestions would be greatly appreciated.

Puzzled,

Matt
 
The only time I have found that I didn't need to enter the parameter data
types was when I specified values in the Column Headings property. Other
than entering the parameter data types, I have found that crosstab query
filter is the same as any other query.
 
Thanks for the answer. I didn't make it clear that after the parameter errors
disappeared, the crosstab ran but returned nothing, no error, just no data. I
know that data should appear because I get it in the query I based the crosstab
on.

What kind of mistake is one making when a query returns no data when it should?
It sounds like a spelling mistake, but I can't find any.

Thanks,

Matt



The only time I have found that I didn't need to enter the parameter data
types was when I specified values in the Column Headings property. Other
than entering the parameter data types, I have found that crosstab query
filter is the same as any other query.

--
Duane Hookom
MS Access MVP
--

DOYLE60 said:
I am trying to do a crosstab query based on another query which has a
filter on
it. The filter was the following:

Between [Forms]![DialogPrintfrm]![DateLow]+1 And
[Forms]![DialogPrintfrm]![DateHigh]

When trying to run the crosstab, the database gave me an error: "The
Microsoft
Jet Database engine does not recongize
"[Forms]![DialogPrintfrm]![DateLow]" as
a valid field name or expression."

So I put in the expression in the parameters along with the DateHigh. And
I
ran the crosstab. It worked. I then went back to the query the crosstab
is
based on and added more filters. Ones like this:

Like
(IIf(IsNull([Forms]![DialogPrintfrm]![MatchChosen]),"*","|[Forms]![DialogP
rintfrm]![MatchChosen]|"))

And it worked without giving me parameter problems. Surprising but true!
I
put in four other filters like the one above and checked them all out.
They
worked.

But I went back to the database a few days later and for some reason the
crosstab didn't work. It gave me the old message again about not
recognizing
the filters. The subsequent ones. So after I put the troublesome filter
in
the parameters, it would give me the next one it had issues with, then the
next, and the next.... I put them all in one at a time, and when I ran
it, it
gave me nothing.

So why does it work one day and not the next? How does one work with
filters
in Crosstabs. I just don't get it. In fooling around with them, trying to
solve this issue, all sorts of wierd things are happening.

The only way I see around it is to build a Make Table query between the
two
queries; have the query with filters, make a table, and then the crosstab
based
on the table. That would negate the filters.

Any help and suggestions would be greatly appreciated.

Puzzled,

Matt
Click to expand...
Click to expand...
 
Could be a spelling mistake or a wrong join between fields. We can't see
your tables, data, sql, ... so it is impossible for anyone to be much more
helpful.

--
Duane Hookom
MS Access MVP
--

DOYLE60 said:
Thanks for the answer. I didn't make it clear that after the parameter
errors
disappeared, the crosstab ran but returned nothing, no error, just no
data. I
know that data should appear because I get it in the query I based the
crosstab
on.

What kind of mistake is one making when a query returns no data when it
should?
It sounds like a spelling mistake, but I can't find any.

Thanks,

Matt



The only time I have found that I didn't need to enter the parameter data
types was when I specified values in the Column Headings property. Other
than entering the parameter data types, I have found that crosstab query
filter is the same as any other query.

--
Duane Hookom
MS Access MVP
--

DOYLE60 said:
I am trying to do a crosstab query based on another query which has a
filter on
it. The filter was the following:

Between [Forms]![DialogPrintfrm]![DateLow]+1 And
[Forms]![DialogPrintfrm]![DateHigh]

When trying to run the crosstab, the database gave me an error: "The
Microsoft
Jet Database engine does not recongize
"[Forms]![DialogPrintfrm]![DateLow]" as
a valid field name or expression."

So I put in the expression in the parameters along with the DateHigh.
And
I
ran the crosstab. It worked. I then went back to the query the
crosstab
is
based on and added more filters. Ones like this:

Like
(IIf(IsNull([Forms]![DialogPrintfrm]![MatchChosen]),"*","|[Forms]![DialogP
rintfrm]![MatchChosen]|"))

And it worked without giving me parameter problems. Surprising but
true!
I
put in four other filters like the one above and checked them all out.
They
worked.

But I went back to the database a few days later and for some reason the
crosstab didn't work. It gave me the old message again about not
recognizing
the filters. The subsequent ones. So after I put the troublesome
filter
in
the parameters, it would give me the next one it had issues with, then
the
next, and the next.... I put them all in one at a time, and when I ran
it, it
gave me nothing.

So why does it work one day and not the next? How does one work with
filters
in Crosstabs. I just don't get it. In fooling around with them, trying
to
solve this issue, all sorts of wierd things are happening.

The only way I see around it is to build a Make Table query between the
two
queries; have the query with filters, make a table, and then the
crosstab
based
on the table. That would negate the filters.

Any help and suggestions would be greatly appreciated.

Puzzled,

Matt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top