Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access is adding ". AS [%$##@_Alias]" and creating strange error

Posted on 2003-12-04
8
Medium Priority
?
1,461 Views
Last Modified: 2008-02-01
Access XP is making changes to a saved query, seemingly all by itself. The query below runs fine. I need to convert it into an Update query. This has happened several times now:  I save it, copy the SQL into a new query, and if I make any changes TO EITHER QUERY, **BOTH** of them acquire the ". AS [%$##@_Alias]"   Worse, after it acquires the Alias statement, it will no longer allow any changes to the SQL at all - running it afterwards produces an error that says the query name is invalid, and it infers the name to be the entire text of the SQL statement.  Does this sound like a virus, a problem somewhere in Access, or have I offended the SQL gods somehow?

SELECT COUNT(dbo_History.CommuterID) AS IndMatching
FROM [SELECT dbo_History.CommuterId, dbo_History.ReportDate, Count(dbo_History.CommuterId) AS CountOfCommuterId, dbo_History.UserCode, dbo_History.Printed
FROM dbo_History
GROUP BY dbo_History.CommuterId, dbo_History.ReportDate, dbo_History.UserCode, dbo_History.Printed
HAVING (((dbo_History.ReportDate)= Date()) AND ((dbo_History.UserCode)="webusr"))]. AS [%$##@_Alias];
0
Comment
Question by:DKJensen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 9878355
I've seen it too.  
What you CAN do is just alias it yourself and thus avoid Access needing to add the garbage.

Since you are using a query as a source of a query it has to call it SOMETHING.  Either you can name it, or it will.
Not a real big deal in my experience.

Walt

0
 

Author Comment

by:DKJensen
ID: 9879055

A) Tried that - it won't accept an alias.  It seemed to, when I added the alias 'as IndMatching,' but even that went away after I tried to revise the query.

B) It's a big deal if it also corrupts the original when I'm working on a copy.  What the?

C) It's a bigger deal if I can't get it to accept any changes after it adds the alias - renders it pretty useless.



0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9879901
Hi DKJensen,

Is this an ADP or an MDB with a connection to a sql server catalog?
When you refer to a 'saved query' are you referring to a View?

Alan
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DKJensen
ID: 9882230
Access is serving as a front end to a FoxPro database.  As to 'saved query,' I meant that I had tested it as a Select query, saved and named it in the Queries screen, then made a copy of it to convert the copy to an Update query.  It was when I was attempting to convert it to an Update that the Alias thing started.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9885450
Some discussion here:
http://www.experts-exchange.com/Databases/MS_Access/Q_20617276.html
http://rsl.gis.umn.edu/~gliang/resac/web/access.html
http://dbforums.com/arch/108/2002/5/369185

It seems the AS [%$##@_Alias]; is a behind the scenes thing that Access does, noramally hidden from the user. Apparently Access does this to all queries but does not usually display the aliasing in the query grid.
Personally I have never come accross this before, but here seems to be quite a few posts on the subject found by searching google for 'AS [%$##@_Alias];' hmm...

Just a shot in the dark here as I am unable to reproduce this behavior on my machines.
My guess is that Access and/or Jet is trying to Alias the Alias, so maybe if you removed the Alias before saving the query, it may work. Also not sure about the square brackets around the sub-select, the norm is round brackets.

SELECT COUNT(dbo_History.CommuterID) AS IndMatching
FROM (SELECT dbo_History.CommuterId, dbo_History.ReportDate, Count(dbo_History.CommuterId) AS CountOfCommuterId, dbo_History.UserCode, dbo_History.Printed
FROM dbo_History
GROUP BY dbo_History.CommuterId, dbo_History.ReportDate, dbo_History.UserCode, dbo_History.Printed
HAVING (((dbo_History.ReportDate)= Date()) AND ((dbo_History.UserCode)="webusr")))

hth
Alan :)


0
 

Author Comment

by:DKJensen
ID: 9889820
I suspected it was the nested query that was causing this, so I've redesigned the approach to avoid this altogether - this was part of a series of queries ('squeries,' I guess), so now I've got them creating temp tables and using a second query to gather the results from each of the tables. Works fine.

I did try removing the Alias before saving, but that odd error kept coming up - it infers the entire contents of the SQL as the name of the query, then declares the name invalid. That's why it was a big deal - it just stopped the query cold. I keep wondering if the FoxPro interface had anything to do with this.

As to the square brackets, Access did that - they were round when I placed them. It also added a period after the bracket, just before the Alias statement.

Oh well - I guess with as many people as use Access, we're going to run into oddnesses occasionally. Wierd happens.

Thanks, everyone.
Deb
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 1500 total points
ID: 9889845
Stranger than strange? hmm.. Glad you got it sorted Deb!

Thanks for the feedback.

Alan :)
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9922317
Me too Deb.  
In the interest of keeping our database clean, could I ask you to either award Alan the points or request a refund in Community Support?

Thanks
Walt
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question