ODBC Issue using Select satement from VB

andyjarvis

:shock: I am connecting a little VB app to a navision 3.10 database using the ODBC Driver however a couple of the fields i need to read from a select satement have "." in them for example the Numbering field of our Employee table is "No."
When i run the select satement

rsemp.Open "SELECT * FROM Employee where No. ='" & Rs1!Employee_No & "'", NavisionConnect, adOpenForwardOnly

This returns a run time error as the ODBC Driver cannot cope with the "."
I cannot obviously go about changing the field in an established version, I was hopeing some one had a solution for me on this one!!!

Hope you can help
Andy

alanperu

Hi,

I have only done this in 2.01, but I'm sure it's pretty similar in 3.10.

In the options of the ODBC Data Source you should be able to set the identifiers. The default is 'All Except Space', but if you change this to 'a-z,A-Z,0-9,_' you can then replace any character that is not in the range 'a-z,A-Z,0-9' with an underscore.

Your statement should now read:

SELECT * FROM Employee WHERE No_ = '.....'

This should work.

You can also use this to replace spaces, so a field such as 'No. of Users' would read as 'No__of_Users' in the SQL statement.

These options are available for the C/ODBC driver, if you are not using this one, then I'm not sure what options you will have for the character set.

Hope this helps.

andyjarvis

Thankyou very very much, problem solved, I knew it worked for spaces just hadnt tried it for "."'s!!!

Thanks
Andy

watung

CMIIW, when a field name contains spaces, use double-quotes:

select "Field Name" from "Table Name"

or in the above example:

rsemp.Open "SELECT * FROM Employee where ""No."" ='" & Rs1!Employee_No & "'", NavisionConnect, adOpenForwardOnly

andyjarvis

no that doesnt help - the _ is the way forward.