VBS Timberline Pervasive sql syntax problem

[Mortheras]

I'm almost finished with a VBA program but one field name is stopping everything. I cannot change the field name in the database. I’m using word 2003 it’s a Timberline DB running on pervasive SQL I’m using the Timberline ODBC driver.

This is the part that’s throwing the error:

       sublicense = rs("Contractor's_License_#")

Guessing the problem is with ‘ and/or # being in the name. But as I said I can’t change the name and I’m unsure of the proper syntax.

Any help is greatly appreciated

 

[JoeAtWork]

I don't know much about Pervasive, but there's few database systems I've encountered that allows a single quote character in a field name. Are you sure that's really the name of the field?

In any case, this might work:

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

 

[JoeAtWork]

The other thing you could do is avoid the name of the field by using the index position instead, for example if it's index position 5, then

sublicense = rs(5)

However, that's not ideal as now you must be careful to never change the field order in the SQL statement that opens the recordset.

 

[HarleyQuinn]

If you're generating the SQL yourself could you not alias the column so you can use the alias rather than the rather badly named column?

I've not worked with pervasive SQL but what I've found on t'interweb seems to tell me you can use a column alias similar to other flavours of SQL.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Mortheras]

Thanks for the help

    sublicense = rs(5)

This worked and no I'm not 100% sure on the field name. It's the field name givin by timberline on a table printout. But another field name Misc Title 3 was actually Job Name and thats the text label by the field in timberline not whats referanced in the table printout. sometimes I think sage just trys to make things harder than they should be.

once again thanks for your help