Allen Browne said:Okay, Bob, the query contains the reserved word DATE as a field name.
Although it is qualified with the table name, it may be where A2007 is
having difficulty interpreting the query.
Enclosing the bad field name in square brackets might be enough to help
Access sort it out. Month and Day are also potential problem names in some
contexts.
Additionally there is a function call to Now(). This could also fail if
the new database has a problem with references. Since Now() includes a
time component, I suspect you are more likely to get the result you want
if you use Date() rather than Now(). And just in case Access is struggling
with the numeric expression, you could try DateAdd().
Putting all that together, create a new query, cancel the Add Table
dialog, and then switch the query to SQL View. Paste in the statement
below. Hopefully you will now be able to switch this query to Design View
okay:
SELECT [Radio Frequencies].[RADIO_FREQ],
[Radio Locations].[MONTH],
[Radio Locations].[DAY],
[Radio Frequencies].[ANIMAL_ID],
[Radio Locations].DRAINAGE,
[Radio Locations].COMMENTS,
[Radio Locations].[DATE]
FROM [Radio Frequencies] INNER JOIN [Radio Locations]
ON ([Radio Locations].SPECIES = [Radio Frequencies].SPECIES)
AND ([Radio Frequencies].[ANIMAL_ID] = [Radio Locations].[ANIMAL_ID])
WHERE (([Radio Locations].[DATE] Between Date() And DateAdd("d", -30,
Date()))
AND ([Radio Locations].SPECIES = "sheep")
AND ([Radio Frequencies].[STATUS_COD] = "Working in Field"))
ORDER BY [Radio Frequencies].[RADIO_FREQ];
I got this from A2007 as you suggested:
SELECT [Radio Frequencies].RADIO_FREQ, [Radio Locations].MONTH, [Radio
Locations].DAY, [Radio Frequencies].ANIMAL_ID, [Radio Locations].DRAINAGE,
[Radio Locations].COMMENTS, [Radio Locations].DATE
FROM [Radio Frequencies] INNER JOIN [Radio Locations] ON ([Radio
Locations].SPECIES = [Radio Frequencies].SPECIES) AND ([Radio
Frequencies].ANIMAL_ID = [Radio Locations].ANIMAL_ID)
WHERE ((([Radio Locations].DATE) Between Now() And Now()-30) AND (([Radio
Locations].SPECIES)="sheep") AND (([Radio
Frequencies].STATUS_COD)="Working in Field"))
ORDER BY [Radio Frequencies].RADIO_FREQ;
Thank you for your effort in trying to resolve this issue. If it would be
any help, I could e-mail you the database.
--
Respectfully,
Bob Brannon
Switch the problem query to SQL View (View menu in query design), and
paste
the SQL statement here.
Presumably you can do that in A2000.
Alternatively, you may be able to get the SQL statement in A2007 opening
the
Immeidate Window (Ctrl+G) and entering:
? CurrentDb.QueryDefs("Query1").SQL
substituting your query name for Query1.
I checked and the file location is a trusted location. I imported the old
database into a new blank database, including all objects, and the same
problem occurs.
Can we assume that you put your database is a "trusted" location? If not,
you may have some security problems, where the code won't run.
If that is not the issue, it sounds like it could be time to get Access to
recreate the database for you. Create a new (blank) database, and import
everything from the Access 2000 database.
OK, I tried your suggestions (they will open in SQL view and run fine).
However, when I switch to Design view I get the same error again. The
queries do run fine, they just won't open in design view.
Thanks for the reply. I'll get to this next week and report back.
Okay, the quotes at the beginning indicate that Access 2007 is confused
about the name of something.
Suggested sequence:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
3. Try the query again.
4. If it still fails at this point, open the Immediate Window (Ctrl+G),
and
enter:
? CurrentDb.QueryDefs("Query1").SQL
substituting your query name for Query1.
When you press Enter, it should print the SQL statement of the query into
the Immediate Window. Copy this to clipboard.
5. Create a new query (no tables.)
Switch it to SQL View (View menu.)
Paste in the SQL statement.
Fix any line endings (e.g. the Immediate window gave you multiple lines
that
broke in the middle of a field name.)
6. Test.
7. If it still fails, paste the SQL statement in a reply to this thread.
If you still have Access 2000 on your computer, at step 4 above you could
open the query in Access 2000, switch to SQL View (view menu), copy to
clipboard (Ctrl+C), switch to Access 20007, and paste the query statement
there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hello Allen,
I was thinking maybe there was some general reason why a query wouldn't
open
in design view in Access 2007 B2TR when it would in Access 2000. Sorry
for
the oversight.
The exact 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.
--
Respectfully,
Bob Brannon
Someone might have a suggestion, Bob, if you could tell us what the error
message says.
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.
Any suggestions on how to get it open in 2007?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.