SQL Syntax Error

[Cheryl]

I am querying an external database and get a syntax error message that looks
like:

SELECT CMT REGISTER_TRANSACTION.Bank_Account,
CMT_REGISTER_TRANSACTION.Check<<???>>, etc, etc.
FROM CMT_REGISTER_TRANSACTION CMT_REGISTER_TRANSACTION
WHERE (CMT_REGISTER_TRANSACTION.Bank_Account='4001')
Syntax Error.

The problem seems to be the "check" field because if I remove it, the data
is retrieved. Have checked the ODBC connections and also confirmed if there
are custom descriptions for the "check" field. Everything seems to be in
order. Have also tried querying other tables in the external database & have
no problems. Wondering what could be causing Excel to return a syntax error
message on this field.

Thanks,
Cheryl

 

[Smallweed]

Probably too simple an explanation but could it be that there's a field with
a name that starts with "Check" and has a space (e.g. "Check Box")? If so,
it will need square brackets around it, eg
SELECT CMT REGISTER_TRANSACTION.Bank_Account,
CMT_REGISTER_TRANSACTION.[Check Box], etc

 

[urkec]

If you are talking about SQL Server, CHECK is a reserved word, so if you have
a column in your table with that name, try using SELECT [Check] from...

Hope this helps.

 

[Cheryl]

Tried out your suggestion and altered the field name, but none worked. Also
ran a test thru Access, bringing in the same table to view the properties and
the field is called "check". Hmm, this one is a puzzle. Thanks a lot for
the suggestion and your time.

Probably too simple an explanation but could it be that there's a field with
a name that starts with "Check" and has a space (e.g. "Check Box")? If so,
it will need square brackets around it, eg
SELECT CMT REGISTER_TRANSACTION.Bank_Account,
CMT_REGISTER_TRANSACTION.[Check Box], etc

I am querying an external database and get a syntax error message that looks
like:

SELECT CMT REGISTER_TRANSACTION.Bank_Account,
CMT_REGISTER_TRANSACTION.Check<<???>>, etc, etc.
FROM CMT_REGISTER_TRANSACTION CMT_REGISTER_TRANSACTION
WHERE (CMT_REGISTER_TRANSACTION.Bank_Account='4001')
Syntax Error.

The problem seems to be the "check" field because if I remove it, the data
is retrieved. Have checked the ODBC connections and also confirmed if there
are custom descriptions for the "check" field. Everything seems to be in
order. Have also tried querying other tables in the external database & have
no problems. Wondering what could be causing Excel to return a syntax error
message on this field.

Thanks,
Cheryl

 

[MH]

You have missed out the underscore on the first line:

SELECT CMT_REGISTER_TRANSACTION.Bank_Account,

MH