Design View

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];

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

BTW, here's a list of the names that can cause you grief in Access:
Problem names and reserved words
at:
http://allenbrowne.com/Ap****ueBadWord.html

There's more than 1000 of them to avoid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

Well, I really do appreciate all your effort in this problem, unfortunately I tried everything you suggested, just as you suggested it, and the problem persists.

I even went so far as to rename, in Table Design, all the fields that had potential conflicts with Access and that did not effect the problem (even using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message ...
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];

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

When you say References, what do you mean? I did already have Name Autocorrect removed in 2000 and checked 2007 and it was still removed.

Would a call to Microsoft help or do they support this beta, or would it just be a waste?



"Allen Browne" wrote in message ...
I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

The References for your project are discussed he
http://allenbrowne.com/ser-38.html

In general the beta is not supported, though the final release is now out.
You may be able to purchase support.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
When you say References, what do you mean? I did already have Name
Autocorrect removed in 2000 and checked 2007 and it was still removed.

Would a call to Microsoft help or do they support this beta, or would it
just be a waste?

"Allen Browne" wrote in message
...
I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

Hello Allen,

I ran a diagnostic on ACCESS and it found one setup error. Unfortunately, that did not result in any change in getting the error message I have been getting. I also checked references and they seemed fine for that database, or is it for all?

I would like to move to 2007 but don't want to do so and then have to pay to get this error fixed when things work fine in 2000. Any suggestions?

Would support from MS likely find a solution anyway?

BTW - is there some way to receive notification when there are responses to newsgroup strings?
--

Respectfully,
Bob Brannon



"Allen Browne" wrote in message ...
The References for your project are discussed he
http://allenbrowne.com/ser-38.html

In general the beta is not supported, though the final release is now out.
You may be able to purchase support.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
When you say References, what do you mean? I did already have Name
Autocorrect removed in 2000 and checked 2007 and it was still removed.

Would a call to Microsoft help or do they support this beta, or would it
just be a waste?

"Allen Browne" wrote in message
...
I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

Someone else may be able to provide a suggestion.

I've just changes computers, and did not bother setting A2007 beta up on the
new one, as I will have the release version very soon. Therefore can't test
it out for you, but I did not see anything like that in the A2007 beta, and
I did throw quite a range of issues at it.

My preferred way of reading these groups is to use Outlook Express. Set up
an Account of type News, for the server:
news.microsoft.com
You can then subscribe to the groups you want, and press Ctrl+H to view your
own threads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
Hello Allen,

I ran a diagnostic on ACCESS and it found one setup error. Unfortunately,
that did not result in any change in getting the error message I have been
getting. I also checked references and they seemed fine for that database,
or is it for all?

I would like to move to 2007 but don't want to do so and then have to pay to
get this error fixed when things work fine in 2000. Any suggestions?

Would support from MS likely find a solution anyway?

BTW - is there some way to receive notification when there are responses to
newsgroup strings?
--

Respectfully,
Bob Brannon



"Allen Browne" wrote in message
...
The References for your project are discussed he
http://allenbrowne.com/ser-38.html

In general the beta is not supported, though the final release is now out.
You may be able to purchase support.

"BBran" wrote in message
...
When you say References, what do you mean? I did already have Name
Autocorrect removed in 2000 and checked 2007 and it was still removed.

Would a call to Microsoft help or do they support this beta, or would it
just be a waste?

"Allen Browne" wrote in message
...
I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?

Hello,

Hopefully someone is still monitoring this thread.

I just discovered that the 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?
--

Happy New Year,
Bob Brannon




"Allen Browne" wrote in message ...
Someone else may be able to provide a suggestion.

I've just changes computers, and did not bother setting A2007 beta up on the
new one, as I will have the release version very soon. Therefore can't test
it out for you, but I did not see anything like that in the A2007 beta, and
I did throw quite a range of issues at it.

My preferred way of reading these groups is to use Outlook Express. Set up
an Account of type News, for the server:
news.microsoft.com
You can then subscribe to the groups you want, and press Ctrl+H to view your
own threads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BBran" wrote in message
...
Hello Allen,

I ran a diagnostic on ACCESS and it found one setup error. Unfortunately,
that did not result in any change in getting the error message I have been
getting. I also checked references and they seemed fine for that database,
or is it for all?

I would like to move to 2007 but don't want to do so and then have to pay to
get this error fixed when things work fine in 2000. Any suggestions?

Would support from MS likely find a solution anyway?

BTW - is there some way to receive notification when there are responses to
newsgroup strings?
--

Respectfully,
Bob Brannon



"Allen Browne" wrote in message
...
The References for your project are discussed he
http://allenbrowne.com/ser-38.html

In general the beta is not supported, though the final release is now out.
You may be able to purchase support.

"BBran" wrote in message
...
When you say References, what do you mean? I did already have Name
Autocorrect removed in 2000 and checked 2007 and it was still removed.

Would a call to Microsoft help or do they support this beta, or would it
just be a waste?

"Allen Browne" wrote in message
...
I don't know what else to suggest, Bob.

You've verified your References are fine, removed the Name AutoCorrect
issue, changed the reserved names, ensured the location is trusted,
compacted and repaired the database. I can't imagine why A2007 would still
be having difficulties with this.

"BBran" wrote in message
...
Well, I really do appreciate all your effort in this problem, unfortunately
I tried everything you suggested, just as you suggested it, and the problem
persists.

I even went so far as to rename, in Table Design, all the fields that had
potential conflicts with Access and that did not effect the problem (even
using your suggestion with date and dateadd along with the name changes).

So, where do I go from here?


"Allen Browne" wrote in message
...
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];

"BBran" wrote in message
...
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



"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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.


"BBran" wrote in message
...
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" wrote in message
...
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.

"BBran" wrote in message
...
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


"Allen Browne" wrote in message
...
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" wrote in message
...
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?