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

    SQL Statement, Parameters, Criteria

    Hi to All,

    I am using VBA code with For...Next Loop to create eight SQL statements. Below is the code

    Code:
    For ICount = 8 to 1 Step -1
    .... Code
    strSQL = INSERT INTO table1 (col1, col2, col3, …)
    SELECT col1, col2, col3, …
    FROM table2
    WHERE Col3 = ICount;
    .... Code
    NEXT ICount
    When I use the SQL statement in a query, the criteria will be ICount and not the corresponding value which is a number between 1 and 8.


    I should see the number (5 for example) and not ICount.
    Any ideas ?

    Thanks
    Khalil

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Khalil Handal View Post
    When I use the SQL statement in a query, the criteria will be ICount and not the corresponding value which is a number between 1 and 8.
    I should see the number (5 for example) and not ICount.
    The straight answer would be
    Code:
    ...
    strSQL = "INSERT INTO table1 (...)
    SELECT ...
    FROM table2
    WHERE Col3 = " & ICount
    ...
    But why not simply drop the For cycle, and get the same result at once
    Code:
    strSQL = "INSERT INTO table1 (...)
    SELECT ...
    FROM table2
    WHERE Col3 BETWEEN 1 AND 8"
    ...

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    Thank you for the reply.
    I added two more conditions to the WHERE Clause. My code looks like this:
    Code:
    For ICount = 8 to 1 Step -1
    .... Code
    strSQL = INSERT INTO table1 (col1, col2, col3, …)
    SELECT col1, col2, col3, …
    FROM table2
    WHERE fkYears =" & Me.cboSYear.Column(0) & "  AND DateDeparted Is Null AND Col3 = " & ICount;
    .... Code
    db.Execute strSQL, dbFailOnError
    NEXT ICount
    In relation to dropping the For Cycle, I need to execute the following code after each strSQL:
    Code:
    db.Execute strSQL, dbFailOnError
    Will it execute it if I use the suggested BETWEEN 1 AND 8 ?

    Khalil

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Khalil Handal View Post
    Will it execute it if I use the suggested BETWEEN 1 AND 8 ?
    As with your design, when any error occurs, all updates are rolled back too, I don't see any difference (but I never have used this feature).

    Are col3 (and maybe some other fields too) components of some unique index? I can't see any other possible reason the INSERT query returning an error! When yes, then there are ways to insert only entries which don't have any matching unique index values in source table before! (using LEFT JOIN to join source table to query, and then WHERE clause to left out all rows where there is a match.

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

Similar Threads

  1. SQL Statement , parameters
    By Khalil Handal in forum Queries
    Replies: 2
    Last Post: 08-18-2022, 02:27 AM
  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. Crosstab Criteria or Parameters
    By lukewarmbeer in forum Access
    Replies: 3
    Last Post: 08-11-2010, 09:57 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