Solved

"can't bind name" - in crosstab query

Posted on 1998-02-13
16
271 Views
Last Modified: 2012-08-13
I have a 'select query' with one fields criteria set to look at a value on a form -
[forms]![drawings]![SiteName]   - this works fine!

When I change the query to a 'crosstab' query, then click datasheet view, I get this comment:-

can't bind name '[forms]![drawings]![SiteName]'.

How can I get this crosstab query to work?
0
Comment
Question by:susannahbond
  • 5
  • 5
  • 3
  • +1
16 Comments
 
LVL 1

Author Comment

by:susannahbond
ID: 1968359
Its all the points I have
0
 
LVL 3

Expert Comment

by:guillems
ID: 1968360
Try to do a query by code:

  Copy the SQL sentence of the cross-tab query and by code do:
 
  In the form that you want to see the query put a button, and in his click's event put the next code:
(..)
dim sSQL as string
sSQL = "TRANSFORM Min(Tabla1.ciudad) AS MínDeciudad " & _
                " SELECT Tabla1.ID_OBJETO " & _
                " FROM Tabla1 " & _
                 " WHERE (((Tabla1.ID_OBJETO)=" & _                                  str([forms]![drawings]![SiteName])  &" )) " & _
                " GROUP BY Tabla1.ID_OBJETO " & _
                 " PIVOT Tabla1.pais; "

  dim midb as database
  set midb = currentdb()
  dim c as querydef
  set c = midb.querydefs("C_SQL") '-- this a query wildcard
  c.sql = sSQL
(..)
  This code modify the SQL'query
  And the open a the query.
0
 
LVL 1

Author Comment

by:susannahbond
ID: 1968361
Thanks for your help so far

I have copied the SQL sentance into code on a button.
I get 'name not found in this collection'
and the line:- set c=midb.querydefs("C_SQL") is highlighted.
What is this query wildcard
0
 
LVL 3

Expert Comment

by:guillems
ID: 1968362
Yes, I don't explain all.

You must create a new query that contains "nothing" (i.e. put any table and make a "simple" query) Put the name of the  query: C_SQL, and in my code I use this query to change the SQL's property.  Instead to create a new query, ...

Is a query that use like a wildcard

PS. (There are more methods to do this, but I prefer this)


0
 
LVL 1

Author Comment

by:susannahbond
ID: 1968363
I have done what you suggested.  The code works.. it changes the simple select query into a crosstab query.
but i still have the same problem!
If I add the line Set RptRS = c.OpenRecordset()
I get the same error - "can't bind name"
If I run the modified query directly I still get the same error.


0
 
LVL 3

Expert Comment

by:guillems
ID: 1968364
I'm so sorry, but the code works well.

If you want I send you my example. Send me your e-mail

  My e-mail is :  guillems@i6.com
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1968365
A dirty solution: Use a temporary table that is a copy of the tableset included in your crosstab query, have a "normal" query fill it with your criteria, make the crosstab get it's data from the temp table.
0
 
LVL 3

Expert Comment

by:guillems
ID: 1968366
Trygve,

  I don't agree with you, my solution works well. Because I make the query "on line" so the query don't have names like 'forms![fmr]![field]', because I change this name for his value, so this solution it works me well.



0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Accepted Solution

by:
IanHinson earned 130 total points
ID: 1968367
In design view of the crosstab query in the Query Grid select Query|Parameters from the menu.
Add the name Forms!Drawings!siteName as a parameter and set the data type to Text.

0
 
LVL 12

Expert Comment

by:Trygve
ID: 1968368
guillems: I was a bit short with my comment. The colon in "A dirty solution:" was included to point out that MY suggestion was the dirty one; dirty, but easier to understand. I agree perfectly that your solution is the most elegant, in fact the same I would have used. I think perhaps Susannah has included the forms... part inside the string instead of copying your example setup.

IanHinson: The parameter version will not work, did you test it before commenting ?
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1968369
Trygve: Yes. On quite a few occasions I have solved problems with name binding in crosstab queries by setting the parameters.
The SQL for the actual query in question has not been given.

0
 
LVL 3

Expert Comment

by:guillems
ID: 1968370
it's curious, where is Susannahbond?

  What he/she said about our comments?


0
 
LVL 12

Expert Comment

by:Trygve
ID: 1968371
Ian: My test crosstab test query didn't come true when I tried the parameter solution, this is why I asked if you had tested it. Maybe I did something wrong, hmmm.
0
 
LVL 1

Expert Comment

by:IanHinson
ID: 1968372
Trygve:
Fortunately I've usually managed to get my crosstabs working ok in the end, sometimes needing the fix I described.

The problem that occurs with parameters in crosstabs was referred to in the following KnowledgeBase article:

---------------------------------------------------------------------
ACC: "Can't Bind Name '[XXX]'" Error with Crosstab Query
 
PSS ID Number: Q91710
Article last modified on 04-02-1997
PSS database name: ACCESS
 
1.0 1.1 2.0 7.0 97
 
WINDOWS
 

======================================================================
---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
---------------------------------------------------------------------
 
SYMPTOMS
========
 
Moderate: Requires basic macro, coding, and interoperability skills.
 
If you create a crosstab query using an implicit parameter [XXX] in a
WHERE clause (criteria), you may receive the following error message:
 
   In Microsoft Access 97:
 
      The Microsoft Jet database engine does not recognize '[XXX]' as a
      valid field name or expression.
 
   In earlier versions of Microsoft Access:
 
      Can't bind name '[XXX]'
 
If you use an explicit parameter, the query works as expected.
 
CAUSE
=====
 
A crosstab query dynamically generates column names. Therefore, Microsoft
Access cannot tell whether [XXX] is referring to a parameter or a column
name until after the query is bound.
 
When you build a graph, Microsoft Access uses a crosstab to build the data
to graph. If the query is a parameter query, you have to define the
parameters explicitly, as you would have to for a normal crosstab query.
 
RESOLUTION
==========
 
To avoid this error, define [XXX] as an explicit parameter by adding it to
the Query Parameters dialog box.
 
STATUS
======
 
This behavior is by design.
 
--[remainder omitted for brevity]------------------------------------

Your crosstab that won't be fixed using this method sounds like an interesting challenge.  It might have to be split into two queries, or tackled somehow else.


0
 
LVL 1

Author Comment

by:susannahbond
ID: 1968373
Sorry, I only work two days a week (this week only one).  these answers look great, I will digest them and get back to you.
0
 
LVL 1

Author Comment

by:susannahbond
ID: 1968374
Thanks for all your help, sorry about the long delays in replying
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now