CODEWITHSUNDEEP
ms-access
goxarad784
goxarad784

Reputation: 435

Missing Operator in query expression in MS Access

I am trying to run a query that returns letters before the space from an address column

SELECT col1,(Case When CHARINDEX(' ',address) = 0 then address Else LEFT(address,CHARINDEX(' ',address)-1) END) as streetNumber from table1
where stateID = 15

When I run this query in SQL Server Management Studio it runs but running it in Access gives me:

Syntax error (missing operator) in query expression '(Case WHEN CHARINDEX(' ',address) =0 then address Else LEFT(address,CHARINDEX(' ',address)-1) END)

But there are no operators missing. I am not sure why I am getting this error.

Upvotes: 0

Views: 206

Answers (1)

Gustav
Gustav

Reputation: 55831

Use Access SQL and VBA:

Select 
    col1,
    IIf(InStr([address], ' ') = 0, [address], Left([address], InStr([address], ' ') - 1)) As streetNumber 
From 
    table1
Where 
    stateID = 15

Upvotes: 1

Related Questions