Left Function

[jonnymenthol]

Hello,

I have used the left function to provide me with the left x characters of a field, which works fine.

Description: Left([c$products],[diff])

However, I want to query this field to only give me certain descrpitions, but it will not work.
Can anyone let me know if this is possible, or if I'm totally out of luck,

Thanks,

J.

 

[namliam]

If you put the criteria, it should work....

 

[jonnymenthol]

I'm afraid it doesn't - it gives me a an odbc call failed error, the gist of it being :

argument data ntext is invalid for argument 1 of len function......

In fairness I wouldn't expect it to work, as the field is an expression which simply formats to show the first x characters.

I don't really know though.

J

 

[namliam]

It does work in SQL Server, Oracle and Access... without problems...
You can also use stuff like to_char(sysdate,'YYYY') = 2004 without problems in Oracle....

ODBC... Hmz, that puts a different perspective on things... What database (type) are you connecting to?
Are you sure your query is correct?

 

[FoFa]

OK, I maybe going out on a limb here, but it sounds like it is failing because the field does not contain enough characters, or you are specifing not enough. Try an IIF and see if that helps, something like:
IIF(len([c$products])>=[diff] AND [diff] > 0,Left([c$products],[diff]),"")
Just as an example.