MS Access database: Select statement error

Question

I am trying following query in MS Access 2010 database:

Query:

SELECT     ID, Title, Priority, Workflow_Instance_Step_ID:ID
FROM         Task

Error:

Error Source: Microsoft Office Access Database Engine Error Message: Syntax error (missing operator) in query expression 'Workflow_Instance_Step_ID:ID'

I know that field "Workflow_Instance_Step_ID:ID" is giving error as it has ':' operator, but I cant change it as it it coming from share point list.

I have tried '[Workflow_Instance_Step_ID:ID]' but still its giving an error. Please give me your suggestion on the same.

So what is your data actually stored in? MS Access, SQL Server, or Sharepoint? — HK1
– HK1, Commented Jan 10, 2014 at 13:39
Does it give the same error with the brackets? It's possible Access thinks "Workflow_Instance_Step_ID" is an Alias. When you assign an alias to a SELECT statement, access will show it as "Alias: table.field" in the design view. My best guess is the colon is never allowed in a field name, so regardless of what's coming from Sharepoint, the actual field name likely cannot be Workflow_Instance_Step_ID:ID — Trevor D
– Trevor D, Commented Jan 10, 2014 at 13:44

Gord Thompson · Accepted Answer · 2014-01-10 13:56:56Z

1

I am unable to recreate your issue, at least with a native Access table in Access 2010. I created a table named [baz] with a single text field named [foo:bar]

foo:bar
-------
this   
is     
a      
test

and the query

SELECT [foo:bar] FROM baz;

works fine, so a field name containing a colon character does not seem to cause problems for Access SQL as such.

An ODBC linked table to a SQL Server table with the same structure does not cause problems either.

In both cases the queries were run from within Access 2010 itself.

answered Jan 10, 2014 at 13:56

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

donPablo · Accepted Answer · 2014-01-11 05:29:49Z

0

So we might be in diagnosis mode, so lets try

    SELECT * FROM Task

and then look at the FieldNames that come back.

answered Jan 11, 2014 at 5:29

donPablo

1,9691 gold badge13 silver badges20 bronze badges

Collectives™ on Stack Overflow

MS Access database: Select statement error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related