How To: Substring field in Select from Access DB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: How To: Substring field in Select from Access DB

  1. #1
    Doug Guest

    How To: Substring field in Select from Access DB


    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


  2. #2
    Rick Guest

    Re: How To: Substring field in Select from Access DB


    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
    >



  3. #3
    Doug Guest

    Re: How To: Substring field in Select from Access DB


    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
    >>

    >



  4. #4
    Rick Guest

    Re: How To: Substring field in Select from Access DB


    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
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center