Using a field alias in Query does not work with some third-party databases (298955)
The information in this article applies to:
- Microsoft Office Excel 2003
- Microsoft Excel 2002
- Microsoft Excel 2000
- Microsoft Query
This article was previously published under Q298955
Important This article contains information about modifying the registry.
Before you modify the registry, make sure to back it up and make sure that you
understand how to restore the registry if a problem occurs. For information
about how to back up, restore, and edit the registry, click the following
article number to view the article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry SYMPTOMS When you try to use Microsoft Query to return data from
some third-party databases into Microsoft Excel, apostrophes (') around alias
names can cause the query to fail. The error message that you receive depends
on the data source that you use.
CAUSE This behavior is the result of a design change in Microsoft
Query. Apostrophes are now automatically used when aliasing, to prevent errors
that are caused by an alias that contains spaces or reserved words.
RESOLUTIONA supported hotfix is now available from Microsoft, but it is only intended to correct the problem that this article describes. Apply it only to systems that are experiencing this specific problem. To resolve this problem, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site: Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question. Excel 2003 You do not have to download anything to fix this problem in Microsoft Office Excel 2003. See the "More Information" section of this article for instructions about how to enable this functionality Excel 2002 The global Excel 2002
version of this hotfix has the file attributes (or later) that are listed in
the following table. The dates and times for these files are listed in
coordinated universal time (UTC). When you view the file information, it is
converted to local time. To find the difference between UTC and local time, use
the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Size File name
----------------------------------------------------
24-Sep-2002 01:29 2,100,760 Msqry32.msp
24-Sep-2002 02:02 1,331,424 Msqry32_admin.msp
After the hotfix is installed, the following files will have the
listed attributes or later:
Date Time Version Size File name
--------------------------------------------------------------
13-Sep-2002 03:52 10.0.4510.0 739,912 Msqry32.exe
Excel 2000 The global Excel 2000
version of this hotfix has the file attributes (or later) that are listed in
the following table. The dates and times for these files are listed in
coordinated universal time (UTC). When you view the file information, it is
converted to local time. To find the difference between UTC and local time, use
the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Size File name
---------------------------------------------------
08-Nov-2002 13:41 1,166,692 Msqry32.msp
08-Nov-2002 16:36 1,419,628 Msqry32_admin.msp
After the hotfix is installed, the following files will have the
listed attributes or later:
Date Time Version Size File name
--------------------------------------------------------------
19-Sep-2002 20:31 9.0.0.6919 1,089,536 Msqry32.exe
After you install this
hotfix, follow the steps that are listed in the "More Information" section of
this article to set the AllowAlias registry key and to activate the
hotfix. Note After you install the fix that is described in this article, and
set the AllowAlias registry key, Microsoft Query will behave as it did in Excel
97. WORKAROUNDMicrosoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To work around this issue, add quotation marks
to the alias from inside Microsoft Query. To do this, follow these steps:
- Double-click the field name in Microsoft Query.
- In the Edit Column dialog box, type your
field name alias surrounded by quotation marks.
For example, type
"Fred" (with the quotation marks), and then click
OK.
After you have returned the data back to Excel,
you can use Microsoft Visual Basics for Applications (VBA) to modify the SQL
and remove the quotation marks from a QueryTable or from a PivotTable:
ActiveSheet.Querytable(1).SQL = "new sql string"
ActiveSheet.PivotTables(1).Pivotcache.Commandtext = "new sql string"
STATUSMicrosoft
has confirmed that this is a problem in the Microsoft products that are listed
in the "Applies to" section of this article.REFERENCES If you do not have to use aliasing with Microsoft Query,
you can turn off aliasing completely. For additional information about how to turn off aliasing
in Microsoft Query, click the following article number to view the article in
the Microsoft Knowledge Base: 259347
XL2000: Error Message: ORA-00923:FROM Keyword Not Found Where Expected
Modification Type: | Major | Last Reviewed: | 4/7/2006 |
---|
Keywords: | kbQFE KBHotfixServer kbbug kbfix KB298955 |
---|
|