Cross Tab Query with Combo Box as Criteria

[Leo]

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there something
i'm doing wrong?
Please advise.

Thanks

 

[Duane Hookom]

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

 

[Leo]

Thanks Duane! It worked!!

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there something
i'm doing wrong?
Please advise.

Thanks

 

[Karla V]

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!

Thanks Duane! It worked!!

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there something
i'm doing wrong?
Please advise.

Thanks

 

[Karla V]

OK, after doing some more searching, I found Query - Parameter, which I never
knew was there before. I've always typed parameters directly into the
criteria row.

However, I am trying the enter a parameter for a data range so am using
between [forms]![frm_name]![fieldname] and [forms]![frm_name]![fieldname]. I
click OK, save the query, but when I go back in, Access adds a [ at the
beginning of the expression and errors out when I attempt to run my report.
It also puts a ] at the very end. I've tried removing the extra brackets,
saving again, but the same thing happens each time.

Any ideas on how to fix this?

Thanks in advance!

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!

Thanks Duane! It worked!!

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the
following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there
something
i'm doing wrong?
Please advise.

Thanks

 

[Duane Hookom]

I have seen this bug before. You need to view the sql view and fix the
brackets and hope they stay correct when you save the query.

--
Duane Hookom
MS Access MVP
--

OK, after doing some more searching, I found Query - Parameter, which I never
knew was there before. I've always typed parameters directly into the
criteria row.

However, I am trying the enter a parameter for a data range so am using
between [forms]![frm_name]![fieldname] and [forms]![frm_name]![fieldname]. I
click OK, save the query, but when I go back in, Access adds a [ at the
beginning of the expression and errors out when I attempt to run my report.
It also puts a ] at the very end. I've tried removing the extra brackets,
saving again, but the same thing happens each time.

Any ideas on how to fix this?

Thanks in advance!

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!

Thanks Duane! It worked!!

:

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the
following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there
something
i'm doing wrong?
Please advise.

Thanks

 

[Karla V]

I'm afraid I still don't have my crosstab report working! When I view the
underlying queries (I have two that require date parameters) in SQL, remove
the extra brackets we talked about earlier, I have this:

PARAMETERS Between [Forms]![frm_crosstab_report]![date1] And
[forms]![frm_crosstab_report]![date2] DateTime;
SELECT tbl_dealer_name_number.[Dealer Name], tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC, Count(tUCC1_DistrictMaster.EQTSN) AS TotalAll
FROM tUCC1_DistrictMaster INNER JOIN tbl_dealer_name_number ON
tUCC1_DistrictMaster.[DEALER#] = tbl_dealer_name_number.[Dealer#]
GROUP BY tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#], tUCC1_DistrictMaster.EQTDESC;

But, when I try to save, it returns a syntax error in the "parameter
clause." I click OK and then it highlights the first "forms" word in the
parameter. Not sure what is going on here.

My final crosstab report does not contain the actual date field I'm
attempting to filter on. Does that have something to do with the problem?

Also, since I'm new to the Query - Parameter menu, how exactly does Access
know which field I want the parameter to run on? Do I need to somehow
reference the field in the parameter statement? I'm feeling quite confused!

Thanks so much for your help - I don't know where I would be without these
discussion groups! I've gotten countless solutions on other issues and am
hoping I'll find one on this problem too.

I have seen this bug before. You need to view the sql view and fix the
brackets and hope they stay correct when you save the query.

--
Duane Hookom
MS Access MVP
--

OK, after doing some more searching, I found Query - Parameter, which I never
knew was there before. I've always typed parameters directly into the
criteria row.

However, I am trying the enter a parameter for a data range so am using
between [forms]![frm_name]![fieldname] and [forms]![frm_name]![fieldname]. I
click OK, save the query, but when I go back in, Access adds a [ at the
beginning of the expression and errors out when I attempt to run my report.
It also puts a ] at the very end. I've tried removing the extra brackets,
saving again, but the same thing happens each time.

Any ideas on how to fix this?

Thanks in advance!

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!


:

Thanks Duane! It worked!!

:

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the
following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there
something
i'm doing wrong?
Please advise.

Thanks

 

[John Spencer (MVP)]

Duane,

pardon me for jumping in here.

Karla,

The parameters section should only contain the parameter definitions and not the
operators (Between and And). Then in the query you use the parameters along with
the operators to limit the rows you want returned using either a having clause
or a where clause.

PARAMETERS [Forms]![frm_crosstab_report]![date1] DateTime,
[Forms]![frm_crosstab_report]![date2] DateTime;
SELECT tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC,
Count(tUCC1_DistrictMaster.EQTSN) AS TotalAll
FROM tUCC1_DistrictMaster INNER JOIN tbl_dealer_name_number ON
tUCC1_DistrictMaster.[DEALER#] = tbl_dealer_name_number.[Dealer#]
WHERE [SomeTable].[SOMEDATEFIELD]
Between [Forms]![frm_crosstab_report]![date1] And
[forms]![frm_crosstab_report]![date2]
GROUP BY tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC

If this doesn't work for you, POST the SQL of the SELECT query that does work
and then someone can modify it for you.

I'm afraid I still don't have my crosstab report working! When I view the
underlying queries (I have two that require date parameters) in SQL, remove
the extra brackets we talked about earlier, I have this:

PARAMETERS Between [Forms]![frm_crosstab_report]![date1] And
[forms]![frm_crosstab_report]![date2] DateTime;
SELECT tbl_dealer_name_number.[Dealer Name], tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC, Count(tUCC1_DistrictMaster.EQTSN) AS TotalAll
FROM tUCC1_DistrictMaster INNER JOIN tbl_dealer_name_number ON
tUCC1_DistrictMaster.[DEALER#] = tbl_dealer_name_number.[Dealer#]
GROUP BY tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#], tUCC1_DistrictMaster.EQTDESC;

But, when I try to save, it returns a syntax error in the "parameter
clause." I click OK and then it highlights the first "forms" word in the
parameter. Not sure what is going on here.

My final crosstab report does not contain the actual date field I'm
attempting to filter on. Does that have something to do with the problem?

Also, since I'm new to the Query - Parameter menu, how exactly does Access
know which field I want the parameter to run on? Do I need to somehow
reference the field in the parameter statement? I'm feeling quite confused!

Thanks so much for your help - I don't know where I would be without these
discussion groups! I've gotten countless solutions on other issues and am
hoping I'll find one on this problem too.

I have seen this bug before. You need to view the sql view and fix the
brackets and hope they stay correct when you save the query.

--
Duane Hookom
MS Access MVP
--

OK, after doing some more searching, I found Query - Parameter, which I never
knew was there before. I've always typed parameters directly into the
criteria row.

However, I am trying the enter a parameter for a data range so am using
between [forms]![frm_name]![fieldname] and [forms]![frm_name]![fieldname]. I
click OK, save the query, but when I go back in, Access adds a [ at the
beginning of the expression and errors out when I attempt to run my report.
It also puts a ] at the very end. I've tried removing the extra brackets,
saving again, but the same thing happens each time.

Any ideas on how to fix this?

Thanks in advance!

:

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!


:

Thanks Duane! It worked!!

:

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter' by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the
following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select' query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there
something
i'm doing wrong?
Please advise.

Thanks

 

[Duane Hookom]

Thanks John.

--
Duane Hookom
MS Access MVP

Duane,

pardon me for jumping in here.

Karla,

The parameters section should only contain the parameter definitions and not the
operators (Between and And). Then in the query you use the parameters along with
the operators to limit the rows you want returned using either a having clause
or a where clause.

PARAMETERS [Forms]![frm_crosstab_report]![date1] DateTime,
[Forms]![frm_crosstab_report]![date2] DateTime;
SELECT tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC,
Count(tUCC1_DistrictMaster.EQTSN) AS TotalAll
FROM tUCC1_DistrictMaster INNER JOIN tbl_dealer_name_number ON
tUCC1_DistrictMaster.[DEALER#] = tbl_dealer_name_number.[Dealer#]
WHERE [SomeTable].[SOMEDATEFIELD]
Between [Forms]![frm_crosstab_report]![date1] And
[forms]![frm_crosstab_report]![date2]
GROUP BY tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC

If this doesn't work for you, POST the SQL of the SELECT query that does work
and then someone can modify it for you.

I'm afraid I still don't have my crosstab report working! When I view the
underlying queries (I have two that require date parameters) in SQL, remove
the extra brackets we talked about earlier, I have this:

PARAMETERS Between [Forms]![frm_crosstab_report]![date1] And
[forms]![frm_crosstab_report]![date2] DateTime;
SELECT tbl_dealer_name_number.[Dealer Name], tUCC1_DistrictMaster.[DEALER#],
tUCC1_DistrictMaster.EQTDESC, Count(tUCC1_DistrictMaster.EQTSN) AS TotalAll
FROM tUCC1_DistrictMaster INNER JOIN tbl_dealer_name_number ON
tUCC1_DistrictMaster.[DEALER#] = tbl_dealer_name_number.[Dealer#]
GROUP BY tbl_dealer_name_number.[Dealer Name],
tUCC1_DistrictMaster.[DEALER#], tUCC1_DistrictMaster.EQTDESC;

But, when I try to save, it returns a syntax error in the "parameter
clause." I click OK and then it highlights the first "forms" word in the
parameter. Not sure what is going on here.

My final crosstab report does not contain the actual date field I'm
attempting to filter on. Does that have something to do with the problem?

Also, since I'm new to the Query - Parameter menu, how exactly does Access
know which field I want the parameter to run on? Do I need to somehow
reference the field in the parameter statement? I'm feeling quite confused!

Thanks so much for your help - I don't know where I would be without these
discussion groups! I've gotten countless solutions on other issues and am
hoping I'll find one on this problem too.

I have seen this bug before. You need to view the sql view and fix the
brackets and hope they stay correct when you save the query.

--
Duane Hookom
MS Access MVP
--

OK, after doing some more searching, I found Query - Parameter, which I
never
knew was there before. I've always typed parameters directly into the
criteria row.

However, I am trying the enter a parameter for a data range so am using
between [forms]![frm_name]![fieldname] and [forms]![frm_name]![fieldname].
I
click OK, save the query, but when I go back in, Access adds a [ at the
beginning of the expression and errors out when I attempt to run my
report.
It also puts a ] at the very end. I've tried removing the extra brackets,
saving again, but the same thing happens each time.

Any ideas on how to fix this?

Thanks in advance!

:

Duane,

I am having the same problem, but don't completely understand your
instructions. Where do I select Query - Parameters from?

Thanks for your help!


:

Thanks Duane! It worked!!

:

You must select Query->Parameters and enter:
forms!frm_Name!cmb_Box Text (or other data type)

--
Duane Hookom
MS Access MVP
--

Hi -

I have a crosstab query that I would like to be able to 'filter'
by what I
have in my combo box.

I entered 'forms!frm_Name!cmb_Box' under 'Criteria' and I get the
following
error message:
"The microsoft Jet Database engine does not recognize
[forms!frm_Name!cmb_Box] as a valid field name or expression"

However, when I switch my query from a 'crosstab' to a 'select'
query and
run it, it works!

Why does the crosstab query not recognize combo boxes or is there
something
i'm doing wrong?
Please advise.

Thanks