Re: crosstab query parameter error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:%23CaUdWJbGHA.472@xxxxxxxxxxxxxxxxxxxxxxx
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.


"Blair" <bemullen@@@ns.sympatico.ca> wrote in message
news:OCZ6qvIbGHA.504@xxxxxxxxxxxxxxxxxxxxxxx
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" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:O7xWLTIbGHA.3828@xxxxxxxxxxxxxxxxxxxxxxx
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" <bemullen@@@ns.sympatico.ca> wrote in message
news:eouWr5HbGHA.1812@xxxxxxxxxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: crosstab query parameter error
    ... I tried deleting some brackets that didn't look right to me but it didn't ... If this is for your crosstab query, ... first three characters are "cam," such as camaro, camera, cameron, et ...
    (microsoft.public.access.queries)
  • Re: Escape character weirdness
    ... The problem of course is that you can't return brackets from the ... Sql Server uses brackets '' to escape. ... I tried a 'LIKE ' query, ... PreparedStatement p = c.prepareStatement("insert into #foo ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: crosstab query parameter error
    ... If this is for your crosstab query, ... then you don't need brackets, but you need a column to compare to with the ... Reserved words or contain illegal characters to try to slide them past Jet ...
    (microsoft.public.access.queries)
  • Re: Help with Date Parameter
    ... "Enter Business Date of Report"? ... When I run the query, records for all dates are displayed instead of just ... You need to set the query criteria to: ... Brackets will not be necessary (but are still good ...
    (microsoft.public.access.queries)
  • Re: can this be done with a subquery?
    ... Your SQL is fine but it's approximately my first query. ... MaxAs LatestDate ... *further brackets* within a subquery ...
    (microsoft.public.access.queries)