MS Query - Alias syntax

[Guest]

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.

 

[Jamie Collins]

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.

--

 

[Guest]

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.

 

[Jamie Collins]

for some reason the ? does not work with complex queries

I think this is the result of a query not being able to be displayed
'graphically' i.e. loss of support for parameters. You get the
'graphically' message when the query is complex or uses SQL other than
MSQuery's implementation.

Jamie.

--