Results 1 to 3 of 3
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    237

    SQL Statement , parameters

    Hi to all,
    I am using a command button to insert records in a table. I have the following VBA code:
    Code:
    Dim intVal1 as Integer
    Dim intVal2 as Integer
    ....
    strSQL = "Insert INTO Table1 " _
    & "(field1,field2, field3, field4, field5, field6) " _
    & "SELECT field1, field2, field3, field4, intVal1, intVal2 " _
    & "FROM Table2 INNER JOIN Table3 ON Table2.pk = Table1.fk " _
    & "WHERE Condition1 AND Condition2 AND Condition3;"
    
    db.Execute strSQL, dbFailOnError
    intVal1 and intVal2 are numbers taken from a different table: Table4 as follow:

    Code:
    intVal1 = DLookUp("[FieldName1]", "[Table4]", "Criteria")
    intVal2 = DLookUp("[FieldName2]", "[Table4]", "Criteria")
    Using Debug.Print shows the correct numbers for intVal1 and intVal2.
    Running the code gives error 3061: Too few parameter. Expected 2.


    Any ideas?

    Thanks,
    Kahlil

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Well the intvals are not in Table2 are they?

    You will likely need to concatenate those variables into the sql string along the lines of
    Code:
    "The first part of the sql string" & intval & " AS Field5, " & intval2 & " AS Field6"
    Debug.Print strSQL to see what you get before trying to use it, comment out the Execute until you get it right.
    Last edited by Welshgasman; 08-18-2022 at 05:10 AM.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    Yes, the intvals are not in Table2.
    It worked .

    Thank you

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

Similar Threads

  1. Replies: 3
    Last Post: 04-03-2021, 03:10 PM
  2. =iff statement using date parameters
    By patchesohouli in forum Access
    Replies: 16
    Last Post: 07-19-2018, 11:12 AM
  3. SQL statement from passed Parameters
    By Rpschwar in forum Access
    Replies: 9
    Last Post: 09-29-2017, 04:03 PM
  4. Query help. IIF Statement, a few parameters.
    By parkerjallen in forum Queries
    Replies: 2
    Last Post: 11-15-2012, 09:04 AM
  5. Replies: 7
    Last Post: 08-17-2011, 01:49 PM

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