Access 2007/Query issue

[forrestapi]

I go to run one of my normal macros today and get the following error:


" is not a valid name. Make sure that it does not include invalid characters or punctuation and that is not too long.

Then clicking ok I see which action has failed. I note the error number (2950) and that it has failed on the send object of a query.

So upon further investigation of the query that errored, i have gathered the following info:

-I can double click on the query the macro runs and it works fine, however if i right click and go to design view i get the above error again.
-Other random queries in the database do the exact same thing.
-these issues do NOT occur when i open the database on another machine that is running access 2003 (my machine is running 2007 if you didn't get that from the title).
-This all worked perfectly normal yesterday.


I dont know if it will help, but here is the sql of the query.

SELECT DISTINCT AX1492BFRX_SROPRG.PGPGRP, AX1492BFRX_SROPRG.PGAGRP, Left([PGPRDC],6) AS Item, Right([PGPRDC],Len([PGPRDC])-7) AS ItemDescr, AX1492BFRX_SROPRG.PGPCA2, AX1492BFRX_SRBSRO.SRSTHQ AS OnHnd, AX1492BFRX_SRBSRO.SRCUSQ AS OnSO, AX1492BFRX_SRBSRO.SRPICQ AS OnPick, AVAILABLE.LocQty, AVAILABLE.PickQty, AVAILABLE.UnSell, AVAILABLE.Avail
FROM (AX1492BFRX_SROPRG LEFT JOIN AX1492BFRX_SRBSRO ON AX1492BFRX_SROPRG.PGPRDC = AX1492BFRX_SRBSRO.SRPRDC) LEFT JOIN AVAILABLE ON AX1492BFRX_SROPRG.PGPRDC = AVAILABLE.LPPRDC
WHERE (((AX1492BFRX_SROPRG.PGPCA2)<>"N") AND ((AVAILABLE.Avail)=0 Or (AVAILABLE.Avail) Is Null))


ORDER BY AX1492BFRX_SROPRG.PGPGRP, Right([PGPRDC],Len([PGPRDC])-7);





Thanks in advance for any help

[forrestapi]

wow, no replies yet. bump

[Rawb]

wow, no replies yet. bump

Give it time, complex issues like this tend to take longer to solve than others. Try to wait at least a couple of days before bumping a thread (unless you're posting to provide additional information). . .

That said, I've done a little searching around on the interwebs and have found two possible solutions. Let's see if either of them work for you.

The first solution is to make sure your database is in a Trusted location. You can test this by adding an autoexec macro that opens any of the Forms in your db. If the Form opens when Access loads the db, this isn't the problem.

The second solution was to confirm all of your VB References. I'd go ahead and take a look, but seeing as your problem is with a Query, I'd say the first solution is more likely.

If neither of those work, try getting more information: Post code snippits and screenshots of the error itself and any code that it highlights.

[forrestapi]

Thanks for the info. I have already tried the first one from the research i have done. I will try to work with it some today, but im not sure what other info i can give besides screen shots. To me it seems obvious its a 2007 issue, as it works fine in 2003.

[forrestapi]

Just a quick update, as i think I may have found the issue. I recently added a new table to the query. That table is named "500 locations". If i remove that table (using my 2003 so that i can get into the design) and go rerun it using my 2007 it works fine. So my question now becomes, can you not use numerical characters in a query name?

[Rawb]

Ah hah!

That's probably the issue right there. I know Access doesn't like it when Field names start with numbers, so the same is probably true with Table names.

Try renaming the Table to something like Locations_500 and see if the Macro still works.

[forrestapi]

ok so i renamed the query itself, and still get the same issue when its added back. So obviously the issues lies within the table itself. I dont thin kits the headings as they are PRDC, QTY, and ITEM. Looking in the fields themselves im not seeing anything unusual either.

[Rawb]

Try renaming the Table you added, not the Query.

Also, if you have spaces in any of your Table/Field names, be sure to put square brackets ([]) around them in the Query. So you'd end up with [500 Locations] instead of 500 Locations.

I know, this is pretty basic stuff that you probably already know, but I wanna cover all the bases

[forrestapi]

sorry, i got ahead of myself. It was the table that i renamed, not the query. I will double check myself on the [] and spaces.