crosstab query parameter error

[Blair]

The Microsoft Jet database engine does not recognize <name> as a valid field
name or expression.

· You have a parameter in a crosstab query or in a query that a crosstab
query or chart is based on, and the parameter data type isn't explicitly
specified in the Query Parameters dialog box. To solve the problem:

· In the query that contains the parameter, specify the parameter and its
data type in the Query Parameters dialog box. And;
· Set the ColumnHeadings property for the query that contains the parameter.

I have a query with parameters from a form , when I open the query I get the
results. I then used the wizard to make a crosstab query based on the first
query, when it tries to open it comes up with this error. There are no
parameters in the crosstab query and I can't find ColumnHeadings property in
the first query where the parameters are. anyone got any ideas

Thanks Blair

 

['69 Camaro]

Hi, Blair.

I have a query with parameters from a form

The crosstab query creates columns dynamically, so Access can't tell whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Blair]

Thanks Blair
Built a 72 Corvette Years ago

Hi, Blair.


The crosstab query creates columns dynamically, so Access can't tell whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

You're welcome.

Built a 72 Corvette Years ago

One of the best hobbies in the world is rebuilding great cars.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Blair]

Tried That and it worked except for this parameter

Like [Forms]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![Option4])
It says it has invalid bracketing, I have to be honest I don't know much
about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out. But
this looks correct to me, but the query don't like it.
Can you help
Thanks Blair

 

['69 Camaro]

Hi, Blair.

It says it has invalid bracketing

If this is for your crosstab query, then it won't work. And any query that
the crosstab is "using" counts as "for your crosstab query." The crosstab
parameter must be declared explicitly, either with the PARAMETERS keyword
(these steps are described in Tom Wickerath's tutorial) or within the WHERE
clause. For example:

((Option4 LIKE "cam*") OR (ISNULL(Option4) = TRUE))

.. . . which would return all records where Option4 has no value or the first
three characters are "cam," such as camaro, camera, cameron, et cetera. The
criteria must either be "in the queries" or "in one of the tables."
Controls displaying data on form or report are invalid, because they don't
actually store the data. The tables do.

If your crosstab query isn't going to be using these implicit parameters,
then you don't need brackets, but you need a column to compare to with the
LIKE operator. For example (watch out for word wrap):

MyField LIKE Forms!FDailyWheplingReport!Option4 OR
ISNULL(Forms!FDailyWheplingReport!Option4)

about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.

Actually, if your database is designed correctly, there is never a need for
you to type brackets. Brackets are used to deliminate names that are
Reserved words or contain illegal characters to try to slide them past Jet
so that Jet uses them for a name instead of trying to use them for their
intended purpose (in the case of Reserved words) and doesn't choke on
illegal characters. Avoid Reserved words and always use alphanumeric
characters and the underscore character only for your names. For lists of
Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Tried That and it worked except for this parameter

Like [Forms]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![Option4])
It says it has invalid bracketing, I have to be honest I don't know much
about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.
But
this looks correct to me, but the query don't like it.
Can you help
Thanks Blair
in
message news:[email protected]...

Hi, Blair.


The crosstab query creates columns dynamically, so Access can't tell whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Duane Hookom]

Access will at times add extra brackets without your help changing this
[Forms]![FDailyWheplingReport]![Option4]
into
[[Forms]![FDailyWheplingReport]![Option4]]
You can view the SQL to see if this is the case and remove the extra []s.
--
Duane Hookom
MS Access MVP

Hi, Blair.

It says it has invalid bracketing

If this is for your crosstab query, then it won't work. And any query
that the crosstab is "using" counts as "for your crosstab query." The
crosstab parameter must be declared explicitly, either with the PARAMETERS
keyword (these steps are described in Tom Wickerath's tutorial) or within
the WHERE clause. For example:

((Option4 LIKE "cam*") OR (ISNULL(Option4) = TRUE))

. . . which would return all records where Option4 has no value or the
first three characters are "cam," such as camaro, camera, cameron, et
cetera. The criteria must either be "in the queries" or "in one of the
tables." Controls displaying data on form or report are invalid, because
they don't actually store the data. The tables do.

If your crosstab query isn't going to be using these implicit parameters,
then you don't need brackets, but you need a column to compare to with the
LIKE operator. For example (watch out for word wrap):

MyField LIKE Forms!FDailyWheplingReport!Option4 OR
ISNULL(Forms!FDailyWheplingReport!Option4)

about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.

Actually, if your database is designed correctly, there is never a need
for you to type brackets. Brackets are used to deliminate names that are
Reserved words or contain illegal characters to try to slide them past Jet
so that Jet uses them for a name instead of trying to use them for their
intended purpose (in the case of Reserved words) and doesn't choke on
illegal characters. Avoid Reserved words and always use alphanumeric
characters and the underscore character only for your names. For lists of
Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Tried That and it worked except for this parameter

Like [Forms]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![Option4])
It says it has invalid bracketing, I have to be honest I don't know much
about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.
But
this looks correct to me, but the query don't like it.
Can you help
Thanks Blair
in
message news:[email protected]...

Hi, Blair.

I have a query with parameters from a form

The crosstab query creates columns dynamically, so Access can't tell whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link
to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


The Microsoft Jet database engine does not recognize <name> as a valid
field
name or expression.

· You have a parameter in a crosstab query or in a query that a
crosstab
query or chart is based on, and the parameter data type isn't
explicitly
specified in the Query Parameters dialog box. To solve the problem:

· In the query that contains the parameter, specify the parameter and its
data type in the Query Parameters dialog box. And;
· Set the ColumnHeadings property for the query that contains the
parameter.

I have a query with parameters from a form , when I open the query I
get
the
results. I then used the wizard to make a crosstab query based on the
first
query, when it tries to open it comes up with this error. There are no
parameters in the crosstab query and I can't find ColumnHeadings property
in
the first query where the parameters are. anyone got any ideas

Thanks Blair

 

[Blair]

This is the sql wondering if you see a problem?
I tried deleting some brackets that didn't look right to me but it didn't
work.
Thanks Blair
PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime, [Like
[Forms]]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![[Option4])] Bit;
SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWheplingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWheplingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWheplingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Access will at times add extra brackets without your help changing this
[Forms]![FDailyWheplingReport]![Option4]
into
[[Forms]![FDailyWheplingReport]![Option4]]
You can view the SQL to see if this is the case and remove the extra []s.
--
Duane Hookom
MS Access MVP

message news:%[email protected]...

Hi, Blair.

It says it has invalid bracketing

If this is for your crosstab query, then it won't work. And any query
that the crosstab is "using" counts as "for your crosstab query." The
crosstab parameter must be declared explicitly, either with the PARAMETERS
keyword (these steps are described in Tom Wickerath's tutorial) or within
the WHERE clause. For example:

((Option4 LIKE "cam*") OR (ISNULL(Option4) = TRUE))

. . . which would return all records where Option4 has no value or the
first three characters are "cam," such as camaro, camera, cameron, et
cetera. The criteria must either be "in the queries" or "in one of the
tables." Controls displaying data on form or report are invalid, because
they don't actually store the data. The tables do.

If your crosstab query isn't going to be using these implicit parameters,
then you don't need brackets, but you need a column to compare to with the
LIKE operator. For example (watch out for word wrap):

MyField LIKE Forms!FDailyWheplingReport!Option4 OR
ISNULL(Forms!FDailyWheplingReport!Option4)

about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it

out.

Actually, if your database is designed correctly, there is never a need
for you to type brackets. Brackets are used to deliminate names that are
Reserved words or contain illegal characters to try to slide them past Jet
so that Jet uses them for a name instead of trying to use them for their
intended purpose (in the case of Reserved words) and doesn't choke on
illegal characters. Avoid Reserved words and always use alphanumeric
characters and the underscore character only for your names. For lists of
Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Tried That and it worked except for this parameter

Like [Forms]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![Option4])
It says it has invalid bracketing, I have to be honest I don't know much
about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.
But
this looks correct to me, but the query don't like it.
Can you help
Thanks Blair
in
message Hi, Blair.

I have a query with parameters from a form

The crosstab query creates columns dynamically, so Access can't tell
whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link
to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


The Microsoft Jet database engine does not recognize <name> as a valid
field
name or expression.

· You have a parameter in a crosstab query or in a query that a
crosstab
query or chart is based on, and the parameter data type isn't
explicitly
specified in the Query Parameters dialog box. To solve the problem:

· In the query that contains the parameter, specify the parameter and
its
data type in the Query Parameters dialog box. And;
· Set the ColumnHeadings property for the query that contains the
parameter.

I have a query with parameters from a form , when I open the query I
get
the
results. I then used the wizard to make a crosstab query based on the
first
query, when it tries to open it comes up with this error. There are no
parameters in the crosstab query and I can't find ColumnHeadings
property
in
the first query where the parameters are. anyone got any ideas

Thanks Blair

 

[Blair]

Thanks

Hi, Blair.

It says it has invalid bracketing

If this is for your crosstab query, then it won't work. And any query that
the crosstab is "using" counts as "for your crosstab query." The crosstab
parameter must be declared explicitly, either with the PARAMETERS keyword
(these steps are described in Tom Wickerath's tutorial) or within the WHERE
clause. For example:

((Option4 LIKE "cam*") OR (ISNULL(Option4) = TRUE))

. . . which would return all records where Option4 has no value or the first
three characters are "cam," such as camaro, camera, cameron, et cetera. The
criteria must either be "in the queries" or "in one of the tables."
Controls displaying data on form or report are invalid, because they don't
actually store the data. The tables do.

If your crosstab query isn't going to be using these implicit parameters,
then you don't need brackets, but you need a column to compare to with the
LIKE operator. For example (watch out for word wrap):

MyField LIKE Forms!FDailyWheplingReport!Option4 OR
ISNULL(Forms!FDailyWheplingReport!Option4)

about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.

Actually, if your database is designed correctly, there is never a need for
you to type brackets. Brackets are used to deliminate names that are
Reserved words or contain illegal characters to try to slide them past Jet
so that Jet uses them for a name instead of trying to use them for their
intended purpose (in the case of Reserved words) and doesn't choke on
illegal characters. Avoid Reserved words and always use alphanumeric
characters and the underscore character only for your names. For lists of
Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Tried That and it worked except for this parameter

Like [Forms]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![Option4])
It says it has invalid bracketing, I have to be honest I don't know much
about writing this stuff, I cut and past a lot and don't understand
bracketing except it is needed at times and sometimes can figure it out.
But
this looks correct to me, but the query don't like it.
Can you help
Thanks Blair
in
message news:[email protected]...

Hi, Blair.

I have a query with parameters from a form

The crosstab query creates columns dynamically, so Access can't tell whether
that value is a parameter or a column name until after it's bound. The
parameter must be declared explicitly in the query. Please see the
following Web page:

http://support.microsoft.com/?id=209778

For more information on building crosstab queries, please use the link to
Tom Wickerath's step-by-step crosstab query tutorial located on the
following Web page:

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


The Microsoft Jet database engine does not recognize <name> as a valid
field
name or expression.

· You have a parameter in a crosstab query or in a query that a
crosstab
query or chart is based on, and the parameter data type isn't
explicitly
specified in the Query Parameters dialog box. To solve the problem:

· In the query that contains the parameter, specify the parameter and its
data type in the Query Parameters dialog box. And;
· Set the ColumnHeadings property for the query that contains the
parameter.

I have a query with parameters from a form , when I open the query I
get
the
results. I then used the wizard to make a crosstab query based on the
first
query, when it tries to open it comes up with this error. There are no
parameters in the crosstab query and I can't find ColumnHeadings property
in
the first query where the parameters are. anyone got any ideas

Thanks Blair

 

[Marshall Barton]

This is the sql wondering if you see a problem?
I tried deleting some brackets that didn't look right to me but it didn't
work.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime, [Like
[Forms]]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![[Option4])] Bit;
SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWheplingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWheplingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWheplingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

You are confusing criteria and parameters.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime,
[Forms]![FDailyWheplingReport]![Option4] Text

 

[Blair]

Just tried it this morning works great which you new it would, so you don't
have to put the whole code or statement, just have to tell it where to find
the information.
Only thing is I thought an option control was a yes/no field not a text.
Thanks Blair

This is the sql wondering if you see a problem?
I tried deleting some brackets that didn't look right to me but it didn't
work.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime, [Like
[Forms]]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![[Option4])] Bit;
SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWheplingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWheplingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWheplingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

You are confusing criteria and parameters.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime,
[Forms]![FDailyWheplingReport]![Option4] Text

 

[Marshall Barton]

Actually, I didn't know if that would work. You may have
had other issues beyond the garbled Parameter.

I should also have explained that the type I suggested was
just a guess. You should use the type of the Dead field to
minimize any potential confusion during Access automatic
type conversions.
--
Marsh
MVP [MS Access]

Just tried it this morning works great which you new it would, so you don't
have to put the whole code or statement, just have to tell it where to find
the information.
Only thing is I thought an option control was a yes/no field not a text.
Thanks Blair

This is the sql wondering if you see a problem?
I tried deleting some brackets that didn't look right to me but it didn't
work.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime, [Like
[Forms]]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![[Option4])] Bit;
SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWheplingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWheplingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWheplingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

You are confusing criteria and parameters.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime,
[Forms]![FDailyWheplingReport]![Option4] Text

 

[Blair]

Thanks Blair

Actually, I didn't know if that would work. You may have
had other issues beyond the garbled Parameter.

I should also have explained that the type I suggested was
just a guess. You should use the type of the Dead field to
minimize any potential confusion during Access automatic
type conversions.
--
Marsh
MVP [MS Access]

Just tried it this morning works great which you new it would, so you don't
have to put the whole code or statement, just have to tell it where to find
the information.
Only thing is I thought an option control was a yes/no field not a text.
Thanks Blair

This is the sql wondering if you see a problem?
I tried deleting some brackets that didn't look right to me but it didn't
work.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime, [Like
[Forms]]![FDailyWheplingReport]![Option4] Or
IsNull([Forms]![FDailyWheplingReport]![[Option4])] Bit;
SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWheplingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWheplingReport]![Option4] Or

([98MatingRecords].Dead)=IsNull([Forms]![FDailyWheplingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));


You are confusing criteria and parameters.

PARAMETERS [Forms]![FDailyWheplingReport]![Text6] DateTime,
[Forms]![FDailyWheplingReport]![Option4] Text