CODEWITHSUNDEEP
sqlms-accesssubquery
Ric Oldroyd
Ric Oldroyd

Reputation: 23

MS ACCESS Subquery not working - "At Most one record can be returned by this subquery" (convert from T-SQL)

I am trying to move some SQL code to Access (So colleagues can run whilst I'm on holiday!)

What I have in original SQL (MSSQL) is a pretty basic sub-query:

Select Customer_code,
    (SELECT TRIM(EMail) where Email != 'Not Set') AS EMAIL_1
From customers

(Not the full code!)

However, Access doesn't like this, and seems to require a "FROM" clause within the subquery.

So if I use (SELECT TRIM(EMail) FROM Customer where Email <> 'Not Set') AS EMAIL_1

I'm then getting the "At Most one record can be returned by this subquery" error message.

The subquery works fine in SSMS, but I can't work out how to modify this for access.

Any help appreciated (I've tried to look through other questions, but none seem to tie in to exactly what I'm trying to do). There is another subquery on here also, but I have tested separately and they are both erroring in the same way.

Thanks in advance

Upvotes: 0

Views: 226

Answers (1)

GMB
GMB

Reputation: 222432

This subquery without a from clause makes little sense, even in SQL Server: all it does is check the value of email on the current row, so it is equivalent to a case expression. In MS Access, you can just use iif():

select customer_code, iif(email <> 'Not Set', trim(email), null) as email_1
from customers

Upvotes: 1

Related Questions