SQL syntax issue

[Gator]

Been looking at this all day and it’s the only thing keeping my program from working. First the DB I’m working with is proprietary I cannot change the field names. My problem is one of the fields I need to access is named “Contractor’s License #” and I don’t think SQL likes ‘ in a field name and probably doesn’t care for # ether.

So the way I have it now is: sublicense = rs("Contractor's_License_#")
I should know how to fix this but I just can’t remember wish I could just change the name.

Would appreciate any suggestions other than change the field name as that’s not an option

[Chrisax]

To make sure I understand, currently, as it is and with this field name, the database is running under MySQL or what? And you have to write additional code?

[Gator]

The database is running on a pervasive SQL server tied to Timberline. I’m writing a VBA program that access’s the pervasive database with a timberline ODBC drive, retrieves the desired information and fills bookmarks in a word document.

But VBA doesn’t recognize (“Contractor’s_License_#”) as a field/column. My guess is the ‘ and/or # are messing VBA up or maybe even timberline or the pervasive server. The error is “3265” Item not found in this collection. I had the same error for a few other field names but it went away when I replaced spaces with _.

i’m sure it’s a syntax issue but I haven’t figured out how to correct it.

[Gator]

found a work around

Code: Select all

       sublicense = rs(11).Value

But unless you know the 11th Colum is Contractor's License # it won’t make any since to someone else looking at the code later. So no rush now but I would still really like to know how to correct the problem using the field name.

[noobas]

maybe you can use * to delineate a known digit, but not quoting the digit as it has another meaning in VBA/ SQL

I doubt the problem is in VBA though, as long as it's in quotations it shouldn't be a problem.

[vasc]

try one of there

sublicense = rs("[Contractor's_License_#]")

or

sublicense = rs(""Contractor's_License_#"")

the idea is that the final name when passed to your connection driver needs to be enclosed within special characters [] or "" depends on server in general

OR if everyhing phails create a view and be done lol