MS Query - Alias syntax

[David P]

Hi All,

Does anyone know what is the correct syntax to rename a field name in MS
Query? I go thru the wizard and try to modify the sql. The alias or 'as' is
ignored when the query is run.

 

[David P]

Thanks, but it still did not work for me. If it helps, I also had a hard
time calling up the Prompt dialog box. In the graphics, I coded [] in the
value field and that translated into = ? in the sql. This works for simple
query but for some reason the ? does not work with complex queries.

Thanks again, I will keep trying to figure it out.

Does anyone know what is the correct syntax to rename a field name in MS
Query? I go thru the wizard and try to modify the sql. The alias or 'as' is
ignored when the query is run.

In MSQuery syntax, the apostrophe/single quote/Chr$(39) character is
used to delimit the as the alias So, if my SQL looks like this:

SELECT Customers.Address
FROM `C:\Tempo\nwnd4`.Customers Customers

I can edit it to look like this:

SELECT Customers.Address AS Residence
FROM `C:\Tempo\nwnd4`.Customers Customers

When I look at it again, MSQuery has changed it to this:

SELECT Customers.Address AS 'Residence'
FROM `C:\Tempo\nwnd4`.Customers Customers

Unfortunately, the column name is returned to my worksheet with the
quotes. I can workaround this by using the syntax of me RDMS, in this
case MS Access/Jet, rather than MSQuery syntax. So I change my SQL to
this:

SELECT Address AS [Residence] FROM Customers;

My SQL and column name in the worksheet is now how I want it to be. The
down side is that my query can no longer be displayed 'graphically'
(whatever that means), the consequence of which is I lose support for
parameters (which I don't use).

BTW take a look at this KB article in case it applies to you:

Using a field alias in Query does not work with some third-party
databases
http://support.microsoft.com/default.aspx?scid=kb;en-us;298955

Jamie.