Thanks for getting back Allen. I will see what I can do with this tomorrow if I can.
In the meantime, do you have any idea why these would work fine in Access 2000 as is in any view, and not in 2007?
--
Respectfully,
Bob Brannon
Okay, Bob, that looks fairly straighforward.
I'm modified it slightly, by adding square brackets around the Year field
name (to avoid problems), and dropping DISTINCTROW (since the GROUP BY
guarantees that.)
Assuming you still have Name AutoCorrect turned off (important), try
creating a new query. Cancel the Add Table dialog. Switch to SQL View (View
menu.) Paste in the query statement below. Before testing it, save the query
with a new name. Test. Close. See if the saved query works for you.
If that solves the problem, you can delete the one that doesn't work, and
compact the database. Then rename the new one so it uses the old name if you
wish.
If it doesn't solve the problem, we need to start investigating data types.
Open the table [Hunter Days by Ranch and Species] in design view. Select the
Ranch field. What is the data type? (Number?) And Field Size (lower pane.)
Now open [Ranch Acreage by Species] in design view. Is Ranch the same data
type and field size?
Similarly, check the Ranch field is the same type and size in [Ranch Acreage
by Species].
Next, check the data type for the fields [Antelope Hunter #'s], [Deer Hunter
#'s], [Elk Hunter #'s], [Antelope Hunter Days], [Deer Hunter Days], and [Elk
Hunter Days]. Are these all Number types?
If that still doesn't solve it, it would be possible to use fewer groupings,
but it should work as is.
BTW, if these "tables" are actually queries, it may be necessary to go
deeper into those to solve the problem.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
OK, here's the SQL:
SELECT [Hunter Numbers by Ranch and Species].[Year],
[Hunter Numbers by Ranch and Species].Ranch,
[Hunter Numbers by Ranch and Species]![Antelope Hunter #'s] +
[Hunter Numbers by Ranch and Species]![Deer Hunter #'s] +
[Hunter Numbers by Ranch and Species]![Elk Hunter #'s]
AS [Total Hunter #'s],
[Hunter Days by Ranch and Species]![Antelope Hunter Days] +
[Hunter Days by Ranch and Species]![Deer Hunter Days] +
[Hunter Days by Ranch and Species]![Elk Hunter Days]
AS [Total Hunter Days],
[Ranch Acreage by Species].[Total Acres for BMA FTE]
FROM ([Hunter Days by Ranch and Species]
INNER JOIN [Ranch Acreage by Species]
ON [Hunter Days by Ranch and Species].Ranch =
[Ranch Acreage by Species].Ranch)
INNER JOIN [Hunter Numbers by Ranch and Species]
ON [Ranch Acreage by Species].Ranch =
[Hunter Numbers by Ranch and Species].Ranch
GROUP BY [Hunter Numbers by Ranch and Species].[Year],
[Hunter Numbers by Ranch and Species].Ranch,
[Hunter Numbers by Ranch and Species]![Antelope Hunter #'s] +
[Hunter Numbers by Ranch and Species]![Deer Hunter #'s] +
[Hunter Numbers by Ranch and Species]![Elk Hunter #'s],
[Hunter Days by Ranch and Species]![Antelope Hunter Days] +
[Hunter Days by Ranch and Species]![Deer Hunter Days] +
[Hunter Days by Ranch and Species]![Elk Hunter Days],
[Ranch Acreage by Species].[Total Acres for BMA FTE];
I know you wroter earlier about field names, etc. But even with all those
fixed, I would still get the same problem. So I renamed them back to what
they were to save work. If it is any help I could send you a database.
--
Respectfully,
Bob Brannon
Post the SQL statement. If you can't get into SQL View, use the code
suggested in an earlier response.
I can't imagine what's going on here. I haven't seen this problem.
I still have the same problem with the trial version. So how do I get help
with this? I do not want to buy 2007 and then find I have to do a bunch of
work to get my databases to work right in it.
I guess I'll use the trial version that's now out and see what happens in
that and go from there. I'll get back on this either way if you will still
be monitoring this thread?
RTM it "release to manufacturing." In other words, I created that query not
with the beta, but with the final version (identical to what will be on the
DVD in the box when you can buy Office 2007.)
Since you can use the query, it's hard to know where the problem is with
your installation. Presumably you've tried a reinstall. Were you able to
create another new multi-table query in that accdb?
As I said, it's probablly not worth spending lots of time solving this,
since the beta expires soon, and you now at least know that the final
release does not have the problem.
Thanks for sending the trial database. I had no problem opening that query
in design view.
I guess I don't know what the problem is. Every database I have that has a
query based on more than one query or table has this problem. Does that
mean there is something wrong with all of them and if so, how do I find out
what it is?
One other question, what is RTM?
--
Respectfully,
Bob Brannon
Sorry, Bob. I did not see anything like that on Beta 2, though I did not
install TR2. And I certainly don't see that on the RTM release.
Here's something to try:
http://allenbrowne.com/temp/BBran.zip
The zip contains BBran.accdb, created with the RTM.
It's just 2 tables and a query that uses them both.
No code or macros, so it does not need to be trusted.
If that fails when you open it, there is a problem with your installation of
Access. If you can open it, then perhaps the problem is either with the
specific database, or with the way TR2 is writing queries, though I feel
sure others would have experience that if it were generic.
In any case, the beta expires in a few weeks, so I guess you will be moving
to the RTM.
Hello Allen,
This is a follow-up of the thread I started on 11/9/2006 (Design View) which
you responded to so many times (thank you).
I actually just the other day wrote the query in question in design view in
Access 2007 B2TR. It worked and still does work fine. However, as long as
I did not save it I could stay in Design View and work on it. As soon as I
saved it I could not go back into it in design view. That is why I made
sure it worked right before I saved it.
I discovered by trial and error testing that for any single-table or
single-query query I have, if I add another table or query to it I can no
longer open it in design view. Doesn't matter what the query is or what
query or table I add.
Does this shed any light on what the problem may be?
--
Happy New Year,
Bob Brannon
Hi Bob. Something is clearly wrong here.
Here's a way to try to get the SQL statement from the query that doesn't
work. Open the Immediate Window (Ctrl+G), and enter:
? CurrentDb.QueryDefs("Query1").SQL
replacing Query1 with the name of your query.
If that works, you can:
1. Save the query statement as text (e.g. in Notepad).
2. Delete the query.
3. Make sure the Name AutoCorrect boxes are unchecked (Office Button |
Access Options | Current Database | Name AutoCorrect options.)
4. Compact the database (Office Button | Manage | Compact.)
5. Create a new query. Switch to SQL View. Paste the query statement back
in.
(If it's any encouragement, I'm using Access 2007 (RTM, not beta), and have
hundreds of mult-table queries.)
Hello,
I am using Access 2007 B2T2. I am trying to open a query in design view but
get an error. The same query opens fine in design view in Access 2000.
The exact error message is this (with quotes I added on either end):
"" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long."
Yes, the error begins with just a quotation mark. Of course I can't open
the query to see what the problem is.
I discovered that this problem with viewing a query in Design View is
somehow related to showing more than one table or query in the design of
that query.
ANY query I have that is based on more than one query or table has this
problem. EVERY query I have based on only one table or query opens fine in
Design view.
Does that provide any help to determine what the problem is?
