SQL syntax issue

Many AP members are knowledgeable about computers. Ask questions and share your knowledge here. (Information are supplied without any guarantee. Do not open any URL or file not posted by someone you know.) (All users can read)
Post Reply
Gator
Little Leet
Posts: 25
Joined: Thu Aug 27, 2009 3:19 pm

SQL syntax issue

Post by 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 :?
User avatar
Chrisax
President
Posts: 23045
Joined: Wed Apr 19, 2006 1:08 pm

Re: SQL syntax issue

Post by 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
Little Leet
Posts: 25
Joined: Thu Aug 27, 2009 3:19 pm

Re: SQL syntax issue

Post by 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
Little Leet
Posts: 25
Joined: Thu Aug 27, 2009 3:19 pm

Re: SQL syntax issue

Post by 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.
User avatar
noobas
Leetissimo!
Posts: 4951
Joined: Sat Sep 27, 2008 9:41 am
Location: Canada

Re: SQL syntax issue

Post by 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.
" I Don't Like That Man. I Must Get To Know Him Better." -Abraham Lincoln
User avatar
vasc
Leet
Posts: 82
Joined: Thu Apr 27, 2006 5:10 pm

Re: SQL syntax issue

Post by 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
Vasc 220-20-69 Atrox Enforcer
Vasc1 60-00-00 Atrox Enforcer
AFK atm
Post Reply