Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: "Invalid Procedure Call" Error

    I've been using the following statement in the expression builder of a select query for a year or so, but now I receive the error message "Invalid Procedure Call" upon execution.

    JobTitle: Left([HR_TABLE]![JOB_CODE_DESCR],InStr([HR_TABLE]![JOB_CODE_DESCR],",")-1)

    I'm trying to parse text strings like "Project Manager,VP" and "Finance Manager,VP" contained in JOB_CODE_DESCR. The desired result being "Project Manager" and "Finance Manager".

    Neither my OS (Windows XP Pro) nor my version of Access (2003 SP3) have recently changed but I know receive the above error message. It does work when I change the -1 to 0, however, I'm still left with the comma.

    I'm uncertain why this stopped working/of a successful work-around?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    do you have any fields that start with a comma?

    It's going to complain about trying to return -1 characters of a 1 character string where the 0th position has a comma.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Zero length string?

    Prob a zero length string in the data?

    JobTitle: Left([HR_TABLE]![JOB_CODE_DESCR],InStr([HR_TABLE]![JOB_CODE_DESCR],",")-1)

    If they all end consistantly with ",VP":-

    JobTitle: Replace([HR_TABLE]![JOB_CODE_DESCR],",VP","")

    Should do it?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I neglected to mention, there's only one comma in each of the text strings. Some don't have any. I need to return only the text to the left of the comma when one exists. The text to the right of the comma will vary.

    Examples of current text strings:
    Project Manager,VP
    Admin Assistant,Assc2
    Client Service Mgr,VP
    Vendor
    Service Desk Mgr,Off

    Desired text string returned:
    Project Manager
    Admin Assistant
    Client Service Mgr
    Vendor
    Service Desk Mgr

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by JamesB
    I neglected to mention, there's only one comma in each of the text strings. Some don't have any. I need to return only the text to the left of the comma when one exists. The text to the right of the comma will vary.
    Well there's your problem...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I wish it was that easy. However, the data (text strings) has not changed and this statement worked for well over a year. Additionally, I eliminated all the text strings without a comma and re-ran - received the same error.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Run ONLY the InStr() and make sure you get all positive integer values. Anything that returns 1 or less is going to cause an error.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Point taken - thank you. There were a couple records I missed without a comma (=0). So, using an If/then to cull out the text strings without a comma first, and then truncate/parse the remaining text strings is the answer. Thanks again for your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •