Field Name begins with number - Syntax error (missing operator) when SQL query runs

[bravura]

RUNTIME ERROR 3075 Syntax error (missing operator) in query expression 3DI.


I am tryint to select information from Access table where field begins with a number. All fields i'm selecting work ok but they all begin with letters so i believe there's an issue if field name begins with number.


Is there a workaround other than changing field name in access table?

where colHdrs = 3DI et. al.

sSQL = "SELECT " & colHdrs & " FROM tblX WHERE BL = '" & bl & "';"

 

[Joe4]

While Access does allow for field names to start with numbers, SQL does that. So if you are trying to use SQL code in VBA, or ever think you might need/want to migrate your data to a SQL server someday, you may have problems.


As such, it is wise to follow SQL rules when using Access. Then you shouldn't run into these type of issues. In naming objects/fields, here are some rules you should always follow:
- Only use letters, numbers, and underscores in object/field names. Do not use any other punctuation, including spaces (people use underscores instead of spaces).
- Do not start any object/field names with numbers.


So I would recommend changing your field name to comply with these rules (and change any other fields which violate these rules).

 

[bravura]

Thanks. I'm thinking of using a preceding underscore or other character, maybe that will help. Unfortunately i have to update all macros/SQL that pulls from the table. It has to be a leading number else people will become confused.

 

[Joe4]

I had to do something very similar recently, when I took over a database that someone else designed. There are two things to ease in making these changes:

1. Make object/field name change filter through to other objects (Queries, etc).
To do this, click on the Office button, select Options, then Current Database, and in the Name AutoCorrect Options, check the Perform name AutoCorrect box.

2. In VBA, using Find and Replace, searching across all Modules.

 

[xenou]

You can also put brackets around the field names (if possible):

[3di], [4di], [5di], etc

That might help.

As a rule, as mentioned, I'd try to always begin a field name with a letter and then use only letters, digits, or underscores (no spaces!). For ordinary identifiers, that is. System tables and columns can follow other conventions since they have their own ways of doing things - but that's not up to us.