How To: Substring field in Select from Access DB

[Doug]

I am trying to select only a portion of a field from an Access database.
Here is the statement I wish to execute:

SELECT SUBSTR(NFDESC,1,30) FROM EINFP

The error I get is...

"Undefined Function 'SUBSTR' in expression"

Isn't that the way to do a substring in Access/Jet?

What am I doing wrong???

Doug

[Rick]

In Access you use various vba string functions such as MID(string,start,length),
LEFT(string,length), RIGHT(string,length), etc.

Check Access help.

"Doug" <dartis@hedstrom.com> wrote:
>
>I am trying to select only a portion of a field from an Access database.

>Here is the statement I wish to execute:
>
>SELECT SUBSTR(NFDESC,1,30) FROM EINFP
>
>The error I get is...
>
>"Undefined Function 'SUBSTR' in expression"
>
>Isn't that the way to do a substring in Access/Jet?
>
>What am I doing wrong???
>
>Doug
>

[Doug]

Thanks Rick.

I had checked the help but all the examples showed the LEFT, MID, RIGHT
functions being used in script. I didn't realize they could be used
in SQL queries. I have always used the SUBSTR function on my AS/400
and I didn't even know about the LEFT function in SQL.

Thanks. As usual, I was fishing all around the answer and just didn't
see it.



"Rick" <bob@aol.com> wrote:
>
>In Access you use various vba string functions such as MID(string,start,length),
>LEFT(string,length), RIGHT(string,length), etc.
>
>Check Access help.
>
>"Doug" <dartis@hedstrom.com> wrote:
>>
>>I am trying to select only a portion of a field from an Access database.
>
>>Here is the statement I wish to execute:
>>
>>SELECT SUBSTR(NFDESC,1,30) FROM EINFP
>>
>>The error I get is...
>>
>>"Undefined Function 'SUBSTR' in expression"
>>
>>Isn't that the way to do a substring in Access/Jet?
>>
>>What am I doing wrong???
>>
>>Doug
>>
>

[Rick]

Your Welcome,

Also think about using Left$ to when you want a string. Just Left returns
a variant which then must be converted to a string. Access takes care of
conversion, but why force it to?

- In the spirit of efficient code.

"Doug" <dartis@hedstrom.com> wrote:
>
>Thanks Rick.
>
>I had checked the help but all the examples showed the LEFT, MID, RIGHT

>functions being used in script. I didn't realize they could be used
>in SQL queries. I have always used the SUBSTR function on my AS/400
>and I didn't even know about the LEFT function in SQL.
>
>Thanks. As usual, I was fishing all around the answer and just didn't
>see it.
>
>
>
>"Rick" <bob@aol.com> wrote:
>>
>>In Access you use various vba string functions such as MID(string,start,length),
>>LEFT(string,length), RIGHT(string,length), etc.
>>
>>Check Access help.
>>
>>"Doug" <dartis@hedstrom.com> wrote:
>>>
>>>I am trying to select only a portion of a field from an Access database.
>>
>>>Here is the statement I wish to execute:
>>>
>>>SELECT SUBSTR(NFDESC,1,30) FROM EINFP
>>>
>>>The error I get is...
>>>
>>>"Undefined Function 'SUBSTR' in expression"
>>>
>>>Isn't that the way to do a substring in Access/Jet?
>>>
>>>What am I doing wrong???
>>>
>>>Doug
>>>
>>
>