Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    Boston, MA
    Posts
    2

    Exclamation Unanswered: SQL Append Query, Error 3061 Expecting 1.

    I'm getting Error 3061 "Too few Parameters. Expecting 1.". I'm writing VBA code to build a SQL
    statement behind a userform for a MS Access database.

    I'm building the SQL query in VBA (instead of Access) because its part of a userform to select chemical results based
    on a series of listboxes. Each listbox triggers a query to populate the next listbox to further drill
    down to the precise piece of data the user needs. I'm building a series of tables to later
    construct a table of chemical results. below is the piece of code where I'm hitting the trouble.

    Code:
    Call Build_SQL_SampCollect
    .... Builds Param_List_qry SQL Statement based on user selected criteria. 
    This is simplest search possibility.
     Param_List_qry = sample_collection.sample_id=results.sample_id and ((results.qc_flag) = 'N') and 
    ((SAMPLE_COLLECTION.LOC_ID) = 'EOD3-F19') AND ((SAMPLE_COLLECTION.MATRIX) = 'SOIL') AND ((RESULTS.QC_CODE) = 'SA')) 
    
    OR ( sample_collection.sample_id=results.sample_id and ((results.qc_flag) = 'N') and 
    
    ((SAMPLE_COLLECTION.LOC_ID) = 'EOD3-F19') AND ((SAMPLE_COLLECTION.MATRIX) = 'SOIL') AND ((RESULTS.QC_CODE) = 'DU'))
    
        Master_SampCollect = "INSERT INTO [Ref_SampCollect_Master] (Loc_ID, Sample_ID, Matrix, Samp_Depth_Top, Samp_Depth_Bot, QC_CODE, FILTERED, 
    DU_SAMP_ID, SAMP_DATE, AREA, STUDY_ID) "
        Master_SampCollect = Master_SampCollect + " SELECT DISTINCT loc_id, sample_collection.sample_id, matrix, samp_depth_top, samp_depth_bot,"
    
        Master_SampCollect = Master_SampCollect + " sample_collection.qc_code, filtered, du_samp_id, samp_date, area, study_id"
    
        Master_SampCollect = Master_SampCollect + " FROM cas_list, results, sample_collection"
        Master_SampCollect = Master_SampCollect + " WHERE " + Param_List_qry + ""
    
    ' #### This is where the error message pops up
        CurrentDb.Execute Master_SampCollect, dbSQLPassThrough
    I'm some what experience with Access, and as far as I know I can't generate unique queries based
    on options selected in a userform. I've attached an image of the userform, so you have an idea of
    how many permutations of search options I'm dealing with. I've selected the simplest search criteria,
    which is a single Loc ID and then click "View Results", which is where the above code is triggered.

    So can anyone suggest what the "Expecting 1" error is about. Or why the below code works fine
    in Access but when built in VBA I get the "Expecting 3" error, I suspect it has something to do with
    the JOIN function.

    MS Access SQL statement
    Code:
    SELECT Sample_Collection.Loc_ID, Results.Sample_ID, Sample_Collection.Matrix, Sample_Collection.Samp_Depth_Top,
    
     Sample_Collection.Samp_Depth_Bot, Results.QC_Code, Results.Filtered, Sample_Collection.Du_Samp_ID, Sample_Collection.Sample_Date,
    
     Sample_Collection.Area, Sample_Collection.Study_ID
    
    FROM Sample_Collection INNER JOIN (CAS_List INNER JOIN Results ON (Results.Analysis_Meth=CAS_List.Analysis_Meth) AND 
    
    (CAS_List.CAS=Results.CAS_Num)) ON Sample_Collection.Sample_ID=Results.Sample_ID
    
    GROUP BY Sample_Collection.Loc_ID, Results.Sample_ID, 
    Sample_Collection.Matrix, Sample_Collection.Samp_Depth_Top, 
    Sample_Collection.Samp_Depth_Bot, Results.QC_Code, Results.Filtered, 
    Sample_Collection.Du_Samp_ID, Sample_Collection.Sample_Date, 
    Sample_Collection.Area, Sample_Collection.Study_ID
    
    HAVING (((Sample_Collection.Loc_ID)="EOD3-F19") AND ((Sample_Collection.Matrix)="SOIL") AND ((Results.QC_Code)="SA")) OR 
    
    (((Sample_Collection.Loc_ID)="EOD3-F19") AND ((Sample_Collection.Matrix)="SOIL") AND ((Results.QC_Code)="DU"));
    VBA SQL Statement
    Code:
        Master_SampCollect = "INSERT INTO [Ref_SampCollect_Master](Loc_ID, Sample_ID, Matrix, Samp_Depth_Top, Samp_Depth_Bot, QC_CODE, FILTERED, 
    DU_SAMP_ID, SAMP_DATE, AREA, STUDY_ID) "
        Master_SampCollect = Master_SampCollect + " SELECT loc_id, sample_collection.sample_id, matrix, samp_depth_top, samp_depth_bot,"
    
        Master_SampCollect = Master_SampCollect + " results.qc_code, filtered, du_samp_id, samp_date, area, study_id"
        Master_SampCollect = Master_SampCollect + " FROM [Sample_Collection] INNER JOIN ([CAS_List] INNER JOIN [Results] ON 
    (CAS_List.CAS = Results.CAS_Num)) ON Sample_Collection.Sample_ID = Results.Sample_ID"
        Master_SampCollect = Master_SampCollect + " WHERE " + Param_List_qry
        Master_SampCollect = Master_SampCollect + " GROUP BY loc_id, sample_collection.sample_id, matrix, samp_depth_top, samp_depth_bot, 
    results.qc_code, filtered, du_samp_id, sample_collection.samp_date, area, sample_collection.study_id"
        Master_SampCollect = Master_SampCollect + " ORDER BY loc_id ASC, results.qc_code ASC"
    PS Is there a wal to test SQL code in Access? If I had some thing like SQLPlus for ther DB I could at least test the SQL code directly.
    Attached Thumbnails Attached Thumbnails Search_Form_rev.jpg  

  2. #2
    Join Date
    Nov 2005
    Location
    Boston, MA
    Posts
    2

    Smile Problem found!!

    I wasted close to a week off and on trying to figure out what was wrong with the SQL. the answer.. Nothing!!

    I eventually found that one of the fields for a table was misspelled, thus causing all my problems. Word to the wise, double check your table field names if you're running out of ideas to a bug.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Technically, since the table came first, the error was in the SQL, but glad you found it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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