Re: Design View

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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:%23Boex1TCHHA.3604@xxxxxxxxxxxxxxxxxxxxxxx
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" <bbran@xxxxxxxxxx> wrote in message
news:Oyz6rDOCHHA.3916@xxxxxxxxxxxxxxxxxxxxxxx
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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uoxW28ECHHA.3524@xxxxxxxxxxxxxxxxxxxxxxx
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" <bbran@xxxxxxxxxx> wrote in message
news:uVvxOfACHHA.3836@xxxxxxxxxxxxxxxxxxxxxxx
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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23A1mMp%23BHHA.5068@xxxxxxxxxxxxxxxxxxxxxxx
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" <bbran@xxxxxxxxxx> wrote in message
news:eYBWIL1BHHA.4292@xxxxxxxxxxxxxxxxxxxxxxx
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" <bbran@xxxxxxxxxx> wrote in message
news:%23EC%23ysaBHHA.204@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply. I'll get to this next week and report back.


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23RnALxTBHHA.2140@xxxxxxxxxxxxxxxxxxxxxxx
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" <bbran@xxxxxxxxxx> wrote in message
news:OnHkbWOBHHA.2304@xxxxxxxxxxxxxxxxxxxxxxx
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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%232ewO1JBHHA.992@xxxxxxxxxxxxxxxxxxxxxxx
Someone might have a suggestion, Bob, if you could tell us what the error
message says.

"BBran" <bbran@xxxxxxxxxx> wrote in message
news:%23j3mEHEBHHA.3396@xxxxxxxxxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: ADO data control
    ... Another way to test your query would be to try ... instinctively think that the database is probably not normalized. ... written on the topic of normalization, and one you might like is "Database ... Then the SQL statement and the existing WHERE and ORDER BY calculations ...
    (microsoft.public.vb.controls)
  • Re: Design View
    ... Can we assume that you put your database is a "trusted" location? ... Try the query again. ... it should print the SQL statement of the query into ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... Someone else may be able to provide a suggestion. ... I also checked references and they seemed fine for that database, ... Okay, Bob, the query contains the reserved word DATE as a field name. ... you may be able to get the SQL statement in A2007 opening the ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... If it would be any help, I could e-mail you the database. ... Switch the problem query to SQL View, ... you may be able to get the SQL statement in A2007 opening the ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... I also checked references and they seemed fine for that database, ... Okay, Bob, the query contains the reserved word DATE as a field name. ... you may be able to get the SQL statement in A2007 opening the ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)