List box query works in SQL query analyzer 2000 but not in Access 2003

[Webkins]

I have the following Access 2003 query in the row source of a list box on my form:

select Cast(avg(cast(review_quality_score As decimal(18,2))) As Decimal(18,2)) from mwo.dbo.mwo

This query works perfectly in SQL Query analyzer but when I transfer it to Access I get a message error: "syntax error (missing operator). Can anyone see what I am doing wrong ? Thanks.

 

[PHV]

I'd try this:
SELECT CDec(Avg(CDec(review_quality_score))) FROM mwo.dbo.mwo


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Webkins,

Access will run any literal SQL statements natively in Access and not pass the statement directly to SQL server.

There are two basic solutions from Access...

1. Make a SQL Pass Through Query (SPT Query) to pass the SQL statement directly to SQL server and use that instead for your recordsource. - You could make a stored procedure your statement in the SPT query.

2. Link the SQL table(s) in Access and make your query Access compliant (JET Engine for Access 2003 and earlier and ACE for 2007 and later).


PHV's recommendation is for the second; however, her solution does not change the table name to a linked table name as Acces will not allow periods/dots in object names. Although you likely have to change that anyway because you could name your link just about anything. Additionally since you are casting with a precision, for an Access query you may need to use the Round function to achieve the results you expect.

In the general case it is recommended to use SPT Queries because Access makes notoriously bad decisions about requests to the SQL server and too much information is transferred over the network. This being a simple query with one table it probably will not make any difference.