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
SQL syntax issue
Re: SQL syntax issue
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?
Re: SQL syntax issue
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.
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.
Re: SQL syntax issue
found a work around
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.
Code: Select all
sublicense = rs(11).Value
Re: SQL syntax issue
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.
I doubt the problem is in VBA though, as long as it's in quotations it shouldn't be a problem.
" I Don't Like That Man. I Must Get To Know Him Better." -Abraham Lincoln
Re: SQL syntax issue
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
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
Vasc 220-20-69 Atrox Enforcer
Vasc1 60-00-00 Atrox Enforcer
AFK atm
Vasc1 60-00-00 Atrox Enforcer
AFK atm