Syntax error (missing operator) in query expression (1 Viewer)

[Snowflake68]

I am using Access to face off an SQL 2012 database. I am trying to create an auto id that is formatted like this 0000. I understand that you cannot easily format an Identity column but I have found an sql statement that works when I run it within SQL server but I need to have a query within Access itself that will update another column based on the auto id column.

This is the sql statement that I need to run in Access but when I do I get a Syntax error (missing operator) in query expression. Can anyone help me with amending the query so that it works in Access please.

SELECT RIGHT('0000'+CAST(MyColumn AS VARCHAR(4)),4) AS [Identity] FROM MyTable

Hope someone can help please.

 

[Ranman256]

In Access, you don't dim variables in a query, but you can still do this.
Tho Im not sure what your statement is doing.

SELECT RIGHT([MyColumn],4) AS Identity (show only the right 4 chars)

SELECT format([MyColumn],"0000") AS Identity (format as 4 chars)

 

[Minty]

This is simply a display format - you can achieve the same on an Access form using the format command.

 

[Snowflake68]

In Access, you don't dim variables in a query, but you can still do this.
Tho Im not sure what your statement is doing.

SELECT RIGHT([MyColumn],4) AS Identity (show only the right 4 chars)

SELECT format([MyColumn],"0000") AS Identity (format as 4 chars)

Brilliant thanks so much Ranman256, god knows why I didnt think of this myself. Cant see the wood for the trees sometimes