Problem with Aliasing (AS)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Wetmore - 21 Apr 2008 20:30 GMT

I have a link table where the field containing the parent key is named "PKEY".
In the parent table the key is named "KEY".
I wish to build a query to construct a recordset where the have the PKEY value
in a field named "KEY"

This statement works fine:
SELECT PKey FROM tblJoin WHERE (((tblJoin.CKey)=59))

This one does not:
SELECT PKey AS Key FROM tblJoin WHERE (((tblJoin.CKey)=59))

How can I get values from the join table PKEY field into a field named KEY?
I could go back and rename the join table field, but I would like an easier way, if possible.

Thanks, Dave

Reply to this Message

Jerry Whittle - 21 Apr 2008 21:06 GMT

Strange. It works for me in Access 2007.

KEY is a reserved word in SQL. That might be a problem. I thought that
putting either single or double quotes around "KEY" would help but the alias
then keeps the quotation marks.

http://support.microsoft.com/kb/286335/

What happens if you change the alias to something not a reserved word like:

SELECT PKey AS NotKey FROM tblJoin WHERE (((tblJoin.CKey)=59))

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a link table where the field containing the parent key is named "PKEY".
> In the parent table the key is named "KEY".
[quoted text clipped - 11 lines]
>
> Thanks, Dave

Reply to this Message

Jeff Boyce - 21 Apr 2008 21:07 GMT

David

Perhaps you are running into a conflict/confusion on the part of Access. I
don't recall exactly, but suspect that Access treats "Key" as a reserved
word ... so what it means and what it thinks you mean may not match what you
think you mean.

What happens if you change the alias to something else (i.e., not a reserved
word)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I have a link table where the field containing the parent key is named
>"PKEY".
[quoted text clipped - 15 lines]
>
> Thanks, Dave

Reply to this Message

David Wetmore - 21 Apr 2008 21:49 GMT

Changing from "Key" did it.

Thanks

Reply to this Message