Results 1 to 7 of 7
  1. #1
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Query error

    I'm getting the following errior in an Access 2010 Module



    Microsoft Access can't find the object 'SELECT tbl_HPCODEs.LOB, dbo_RVS_AUTH_MASTERS.REQPROV_KEYID, dbo_RVS_AUTH_MASTERS.AUTHPCP_KEYID, dbo_RVS_AUTH_MASTERS.STATUS, dbo_RVS_AUTH_MASTERS.MEMB_KEYID, dbo_RV_AUTH_DETAILS.AUTHNO, dbo_RV_AUTH_DETAILS.PROCCODE, Trim([PROCCODE]) AS P1, dbo_RVS_AUTH_MASTERS.REQDATE INTO tbl_VV_2011_Auths FROM tbl_HPCODEs INNER JOIN (dbo_RVS_AUTH_MASTERS INNER JOIN dbo_RV_AUTH_DETAILS ON dbo_RVS_AUTH_MASTERS.AUTHNO = dbo_RV_AUTH_DETAILS.AUTHNO) ON tbl_HPCODEs.HPCODE = dbo_RVS_AUTH_MASTERS.HPCODE WHERE (((dbo_RVS_AUTH_MASTERS.STATUS)="1") AND ([REQDATE] between #10/01/2011# and #12/31/2011#));'.


    If I copy the query to the query editor it runs fine with no changes. In the module it errors. This is done in a module because the date parameters are coded the start and end date are for the previous quarter. What am I missing??

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the code look like (how are you using the SQL)? By the way, there are functions that can automatically return the first and last days of a quarter, given a base date. You wouldn't need to have the SQL in a module.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Change the quotes around the literal "1" to apostrophes, like: '1'. Status field is text type?

    Show the VBA procedure if still need help.
    Last edited by June7; 02-26-2012 at 09:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Will be back at this monday morning.

    The Sequel is in a module as there are 3 steps required. The first is to get membership data for 15 specific PCP's. Second Step is to get the number of 2 specific procedures those pcp's performed in a quarter. Third is to get the same data company wide and compare them in a single report. If I combine any 2 in a single query the records are duplicated and the counts are way off. That's why I'm doing it in code. The code performs the first query and then puts the data into an excel spreadsheet. performs the second query and puts those results into the same spreadsheet and then again for the 3rd query. The issue is with the second query. It won't run in code. Runs fine in the query editor. Will post the entire subroutine on Monday after I get to work.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why going out to spreadsheet?

    You want all this on one report? Use subreports?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Would be my preference but user requirements are that it must be in Excel. Never could get a clear answer why.

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Ok, Never mind this morning it's working fine.

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

Similar Threads

  1. query SQL error
    By jscriptor09 in forum Queries
    Replies: 1
    Last Post: 01-12-2012, 06:34 PM
  2. Query Error
    By keith701a in forum Queries
    Replies: 1
    Last Post: 12-20-2011, 03:56 PM
  3. Replies: 2
    Last Post: 05-27-2011, 05:55 AM
  4. Replies: 10
    Last Post: 02-02-2011, 05:48 PM
  5. Query Error
    By zephaneas in forum Queries
    Replies: 0
    Last Post: 11-12-2008, 02:40 PM

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