How to use Left function in ADO

[Guest]

I use to be able to use the Left() function in access queries basically to
filter out all records that started with the same left text (say
"Sub-Assembly" - 12 charactors). Left function is not supported in Access
ADO, and it is telling me to use a Convert function.

I am new to ADO, and am not completely understanding it. Can anyone tell me
how to solve this?

Thanks... Dale

 

[Michel Walsh]

You are probably not using Jet, but MS SQL Server. You can use the function
SUBSTRING( string, start, lengthOfSubstring). You have to use CONVERT if the
first argument is not already a string or, as example, if you want to
concatenate a field which is not a string to a string (something that is
done for you, automatically, in Jet:


SELECT integerFieldName + "a" FROM somewhere


would be, in MS SQL Server


SELECT CONVERT(nvarchar(50), integerFieldName) + "a" FROM somewhere




Vanderghast, Access MVP

 

[Guest]

Yes, you are correct. I am using MS SQL Server.
I used SUBSTRING, but it still says I have to convert. I don't need to
concatenate anything, I am just wanting the first 12 charactors.

Exactly what syntax do I use for this when using CONVERT?

Thanks Michel
Dale

 

[Guest]

I figured it out...
Thanks Michel

Dale

You are probably not using Jet, but MS SQL Server. You can use the function
SUBSTRING( string, start, lengthOfSubstring). You have to use CONVERT if the
first argument is not already a string or, as example, if you want to
concatenate a field which is not a string to a string (something that is
done for you, automatically, in Jet:


SELECT integerFieldName + "a" FROM somewhere


would be, in MS SQL Server


SELECT CONVERT(nvarchar(50), integerFieldName) + "a" FROM somewhere




Vanderghast, Access MVP

 

[Michel Walsh]

The syntax is a little bit weird in that the first argument is the data type
you want (if it is a date, you have other options), but without quotes
around it, and the second argument is the field name (or expression) to be
converted. So, to convert into a string of a variable length, up to 50
characters, that looks like:


CONVERT( nvarchar(50), fieldNameHere )



Hoping it may help,
Vanderghast, Access MVP