Query with numeric as first character in field name

Question

I'm running a SQL query through RODBC. I have a field that is giving me problems querying, titled "1YearTotalSpend". It is a numeric field, and after doing some diagnostics I can see no errors in the contents of the field values, which are numeric.

Specifically, the error I receive is: "[RODBC] ERROR: Could not SQLExecDirect...

library(RODBC)
# This works fine, where "AnyOtherSpendField" 
# is a field whose name doesn't start w/ a numeral.
df <- sqlQuery(handle, "select ID, FieldA, FieldB, FieldC
                          from Table.A
                          where Type not like 'NULL'
                          and Code like 'SUB'
                          and Status in ('100','440','222')
                          and AnyOtherSpendField > 0
                          order by AnyOtherSpendField desc ")
# however both of the two options below fail:
"...and 1YearTotalSpend > 0"
"...order by 1YearTotalSpend ")

Given that the rest of my code works, I strongly suspect that the error comes because of the field starting with a 1, rather than "One". Obviously, starting a field with a numeral is terrible data management practice, and not of my own doing.

Answer 1

If identifiers don't start with alphabetic characters or underscore, you need to escape them. In SQL Server, escape them using square braces. So try:

and [1YearTotalSpend] > 0
"...order by [1YearTotalSpend]

I would also suggest that you construct your column and table names so they do not need to be escaped.

Here is good documentation on the topic.