BUG in Access with AS in query

[JetDeveloper]

SELECT WELL_ID, WELL.WELL_NAME AS WNAME, WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.WELL_NAME = CONST_PLOTS.WELL_NAME
ORDER BY WNAME ASC

This query gives me an exception-

OleDbException:
'No value given for one or more required parameters'

It looks like Access has a bug where it does not recognize the WNAME in the
ORDER BY expression is the one in 'WELL.WELL_NAME AS WNAME'

Any idea why it does not work.

Thanks in advance

[mmetzger]

First question is does it work without the AS statement?

[JetDeveloper]

No it doesn't. It says that WNAME is an missing parameter whether the AS is there or not.

'Does not work - bug?

SELECT WELL_ID, WELL.WELL_NAME AS WNAME, WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.WELL_NAME = CONST_PLOTS.WELL_NAME
ORDER BY WNAME ASC


'Does not work - And should not work

SELECT WELL_ID, WELL.WELL_NAME, WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.WELL_NAME = CONST_PLOTS.WELL_NAME
ORDER BY WNAME ASC

'Works

SELECT WELL_ID, WELL.WELL_NAME AS WNAME, WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.WELL_NAME = CONST_PLOTS.WELL_NAME
ORDER BY WELL.WELL_NAME ASC

[erickwidya]

try use [] bracket at WNAME so it looks like this [WNAME]

[JetDeveloper]

It still asks for a value for WNAME.

[erickwidya]

try this one..

SELECT WELL_ID, WELL_NAME AS [WNAME], WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.[WNAME] = CONST_PLOTS.WELL_NAME
ORDER BY [WNAME] ASC
EDIT : if it's not work..can u post the dbase?

[JetDeveloper]

Erickwidya,

The brackets don't help. I cannot post the real databse, but here is a dummy database to test it on.

There is a query called Test Query, which is the one that I'm having problems with.

[DHillard]

This is one of those "everybody defines their own syntax" areas of SQL. Try AS 'ALIASNAME' .

I've noticed that Access, MS-SQL, MySQL and Oracle each have enough difference in SQL syntax that only the most basic SQL commands are portable between them.

[erickwidya]

well it strange and i haven't realize it until now..

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

The SELECT statement has these parts:

Part - Description
predicate - One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
* - Specifies that all fields from the specified table or tables are selected.
table The name of the table containing the fields from which records are selected.
field1, field2 - The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
alias1, alias2 - The names to use as column headers instead of the original column names in table.
tableexpression - The name of the table or tables containing the data you want to retrieve.
externaldatabase - The name of the database containing the tables in tableexpression if they are not in the current database.from that, guess it's only for change the column caption - for display only? not sure though
the code work but it seem like the one u have already..
SELECT [WELL].[WELL_ID], [WELL].[WELL_NAME] AS WNAME, [WELL].[WELL_TYPE], [WELL].[WELL_PURPOSE]
FROM CONST_PLOTS INNER JOIN WELL ON [CONST_PLOTS].[WELL_NAME]=[WELL].[WELL_NAME]
ORDER BY WELL.WELL_NAME;

[jacander]

Try

SELECT WELL_ID, WELL.WELL_NAME AS WNAME, WELL_PURPOSE, WELL_TYPE
FROM WELL INNER JOIN CONST_PLOTS ON WELL.WELL_NAME = CONST_PLOTS.WELL_NAME
ORDER BY WELL.WELL_NAME ASC