Substring Function - error Undefined Function

[Andydr]

Hi I am trying to use SUBSTRING in a query but every time I try to go to the Datasheet view I get "Undefined Function 'SUBSTRING' in expression.
see below as an example,

select CLIENT.[cl:client_id]
,substring((CLIENT.[cl:client]),1,20)
from CLIENT

I am using Access 2002.
Having used SQL against Oracle Databases for several years I am confused.

Thanks

 

[beetee]

I'm not an SQL guru, but Access has a 'mid' function that is likely equivalent to the 'substring' function.

You might try that, although it suprises me that Access wouldn't support what appears to be a standard SQL function.

 

[Andydr]

Thanks, will try that but you are right there is SUBSTRING, which I found but I just cant get it to work in the Query. So I am assuming that I have got the Syntax wrong.

 

[beetee]

For what it's worth, I didn't find substr or substring in the Access help.

Of course, I'm using Access 97

 

[Andydr]

I know, I did find reference to it in one of the forums when searching and the syntax they describe is what I have tried but it doesnt want to work. Many thanks again.

 

[dodge20]

I think beetee has already posted the solution you need. Whenever I want to use substring in access I have always used mid. This has always returned the values I intended, so I wouldn't waste a lot of time trying to get the substring to work properly.

 

[Andydr]

Thanks Guys, will give it a try and let you know.

 

[Andydr]

Thanks Guys that worked fine.
I have now found a web page that gives a comparison of Oracle SQL and Access SQL.

http://www.cse.bris.ac.uk/~ccmjs/ora_sql.htm