Results 1 to 4 of 4
  1. #1
    BSESSIONS is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    2

    Post Invalid procedure call in access report. (only when grouping, source is union query).

    I have a union query that is the source of a report. When I group inside the report I always get a "invalid procedure call. I think it has something to do with the RIGHT and LEN that I am using in the second part of the union query. Any guidance would be appreciated. The union query is as follows:



    Code:
    SELECT IMPACT.[LAST NAME], IMPACT.[FIRST NAME], IMPACT.[WORK DATE], IMPACT.[HOURS REG], IMPACT.[HOURS OT], IMPACT.DEPARTMENT, IMPACT.AGENCY
    FROM IMPACT
    UNION ALL
    SELECT Left([FULL NAME],InStr(1,[FULL NAME],",")-1) AS [LAST NAME], Right([FULL NAME],Len([FULL NAME])-InStr(1,[FULL NAME],",")-1) AS [FIRST NAME], RANDSTAD.[Date Worked], RANDSTAD.[Reg Hours], RANDSTAD.[OT Hours], RANDSTAD.Dept, Left([Agency],8) AS AGENT
    FROM RANDSTAD
    UNION ALL SELECT STAFFMARK.[LAST NAME], STAFFMARK.[FIRST NAME], STAFFMARK.[WORKED DATE], STAFFMARK.[HOURS REG], STAFFMARK.[HOURS OT], STAFFMARK.DEPARTMENT, STAFFMARK.AGENCY
    FROM STAFFMARK;

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you probably have some nulls in your data so the query fails on something like Len([Full Name]) since you cannot return the length of a null. Same goes for left. Also, InStr(1,[FULL NAME],",") will return 0 if "," is not in the string and left will fail since it cannot return -1 characters.

    to protect against nulls, use the nz function, to protect against there not being a "," in the string use the iif function to test there is a "," and assign a value accordingly.

  3. #3
    BSESSIONS is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    2
    Thank You Ajax
    These are good points for me to make some changes. Would these nulls, cause issue at the report level? The query will run, but when I use it as a record source to the report, it will run. It pulls the invalid procedure call only if I group in the report.

    Thank You for the support

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Would these nulls, cause issue at the report level?
    I would expect them to cause an issue at the query level - if they are not, it may be something to do with your report grouping

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Invalid Procedure Call error - MS Access
    By Saleem13 in forum Access
    Replies: 5
    Last Post: 10-23-2013, 11:36 AM
  2. Replies: 9
    Last Post: 08-10-2012, 03:10 AM
  3. Dedupe Query - Invalid Procedure Call
    By icarus in forum Queries
    Replies: 0
    Last Post: 12-07-2011, 08:49 AM
  4. Access 2007 Append Query Invalid Procedure Call
    By forrestapi in forum Queries
    Replies: 1
    Last Post: 05-13-2011, 07:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums