Handling Spaces in Table Names Used as Variables (1 Viewer)

[pooldead]

I have the following code:

tblName = "TableA"
fldName = "User ID"

sqlStr = "SELECT " & fldName & " FROM " & tblName & " WHERE '" & fldName & "' = '" & rs1.fields("Username") & "'"
Set rs1 = currentdb.openrecordset(sqlStr)

It runs fine when fldName doesn't include a space (ex: UserID), but I get the following error when the field has a space in the name, as shown above. What is the likely simple piece of syntax I'm missing here?

Error: "Run-time error '3075': Syntax error (missing operator) in query expression 'User ID'."

 

[Gasman]

Surround them with [] as you would if they were literals.

 

[theDBguy]

Try enclosing the name in square brackets.

Edit: Too slow...

 

[gemma-the-husky]

so

FROM " & "[" & tblName & "]" & " WHERE

or just

FROM [" & tblName & "] WHERE

 

[plog]

sqlStr = "SELECT " & fldName & " FROM " & tblName & " WHERE '" & fldName & "' = '" & rs1.fields("Username") & "'"

Is that the actual code or sample code? I mean you're running a query and the only value returned would be the value you supplied it.

 

[SHANEMAC51]

tblName = "TableA"
fldName = "User ID"
sqlStr = "SELECT " & fldName & " FROM " & tblName & " WHERE '" & fldName & "' = '" & rs1.fields("Username") & "'"
Set rs1 = currentdb.openrecordset(sqlStr)

tblName = "TableA"
fldName = "User ID"

sqlStr = "SELECT [" & fldName & "] FROM [" & tblName & "]" & _
" WHERE [" & fldName & "] = '" & rs1.fields("Username") & "'"
Set rs1 = currentdb.openrecordset(sqlStr)

 

[The_Doc_Man]

The others have pointed to the problem, but I'll explain it a bit more in-depth.

When Access VBA or SQL has to analyze an input string, it does something called "parsing" to identify the elements in that input string. The way to separate elements is to declare certain characters as having a special meaning called a "delimiter." These delimiter characters represent syntax breaks. That is, the delimiter character is forbidden to be part of the syntax of the OTHER things on the line. Things that immediately follow a syntax break are separate from the things before that syntax break. For SQL and VBA, the syntax-break characters are the space and tab.

Therefore, "TableA" is a perfectly legit object name. Doesn't duplicate any of the reserved words and is relatively simple. Has no syntax breaks.

However, "User ID" contains a syntax-break character. That is not ONE thing (like TableA). It is TWO things - User and ID. The missing operator you reported comes from a rule that two object names on a line must be separated from each other by some kind of action syntax like + or - or * or / or & or any other math or logic operator, you get the idea. Access allows you to override this behavior slightly by allowing bracketing using the [ ] characters (square brackets). Then, anything inside the brackets is taken as a single-item name even if it has a funky character somewhere.

While you COULD simply enclose your item names in [] when they have spaces, in the long run it is better to not have the spaces. Makes easier typing 'cause you don't need to remember the [] for the oddball names. We have also seen folks who have used special characters like # or ( and ) in names. Again, not a good idea, since the special characters usually have some special meaning.

So what's the bottom line? Make your names short and free of special characters including space and tab. Less confusion, easier to type, and if you ever need to upgrade your Access backend file to something like SQL server, less chance of having the new backend reject your tables due to bad names.

 

[Pat Hartman]

Add this annoyance to the list of reasons why experts ALWAYS tell you to avoid special characters and embedded spaces in your object names.