Results 1 to 3 of 3

Thread: For/Next loop

  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: For/Next loop

    I would like to populate 20 columns in a same row with 20 values. However when I used the For/Next loop it populated the 1st column in the 1st row but it places the 2nd value in 2nd column in the 2nd row and so on. For each value it places the value in a new row.
    this is what I like to display:
    3Q 2002 1 1 2 2 0 1 0 . . . . .

    but this is what is displaying:
    3Q 2002 1
    1
    2
    2
    ...

    This is my SQL statement

    For i = 0 To 19 Step 1

    StrSQL = "INSERT INTO tblRGAAnalysis (" & i & ") Values (" & a(intRow, i) & ");"
    db.Execute StrSQL
    Next i


    I tried to use UPDATE instead of INSERT INTO but I got syntax error message.

    Does anyone know how I can fix it?

    Thanks!!

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: For/Next loop

    there are two ways....

    1. Query Way
    2 SQL Insert Into.... Way


    1. query way
    this expects that you already have a table with column Quater e.g. "Q3 2002"

    create public function, which returns value based on quarter and based on other value (column number) and then simply create Query and use this function

    Public Function AppendMyValue(InputQuarter As String, InputValue As Byte) As Long
    'Select Case
    ' ..... InputQuater = .... and InputValue = 1 then
    ' AppendMyValue = 222
    End Function

    and then simply create query and use this function

    SELECT tblRGAAnalysis.Quarter, AppendMyValue([quarter],1) AS MyValue1, AppendMyValue([quarter],2) AS MyValue2 FROM tblRGAAnalysis;

    this expects that you already have a table with column Quarter and you just want to see some other values based on quarter.


    2. the other way is to create table with column quarter and three, four or how many columns you want or you can APPEND to existing table ...

    use statement INSERT INTO or statement SELECT myFields INTO NewTable FROM ....



    Sub test()
    Dim strSQL As String
    Dim strQ As String

    strQ = "Q4 2002"
    strSQL = "INSERT INTO tblRGAAnalysis (Quarter, Value1, Value2) Values (" & Chr(34) & strQ & Chr(34) & "," & fnPopul(strQ) & ");"
    CurrentDb.Execute strSQL

    End Sub


    Function fnPopul(inputQ As String) As String

    'create your string (list of values separated by comma) based on your quater
    '...
    ' the final output string has to look like this - even with commas!
    fnPopul = 12, 7
    ' so it fits into SQL statement. you will use your FOR... NEXT here
    End Function


    with this example final SQL string will look like:

    INSERT INTO tblRGAAnalysis (Quarter, Value1, Value2) Values ("Q4 2002",12,7);

  3. #3
    Join Date
    May 2002
    Posts
    395

    Re: For/Next loop

    Originally posted by playernovis
    there are two ways....

    1. Query Way
    2 SQL Insert Into.... Way


    1. query way
    this expects that you already have a table with column Quater e.g. "Q3 2002"

    create public function, which returns value based on quarter and based on other value (column number) and then simply create Query and use this function

    Public Function AppendMyValue(InputQuarter As String, InputValue As Byte) As Long
    'Select Case
    ' ..... InputQuater = .... and InputValue = 1 then
    ' AppendMyValue = 222
    End Function

    and then simply create query and use this function

    SELECT tblRGAAnalysis.Quarter, AppendMyValue([quarter],1) AS MyValue1, AppendMyValue([quarter],2) AS MyValue2 FROM tblRGAAnalysis;

    this expects that you already have a table with column Quarter and you just want to see some other values based on quarter.


    2. the other way is to create table with column quarter and three, four or how many columns you want or you can APPEND to existing table ...

    use statement INSERT INTO or statement SELECT myFields INTO NewTable FROM ....



    Sub test()
    Dim strSQL As String
    Dim strQ As String

    strQ = "Q4 2002"
    strSQL = "INSERT INTO tblRGAAnalysis (Quarter, Value1, Value2) Values (" & Chr(34) & strQ & Chr(34) & "," & fnPopul(strQ) & ");"
    CurrentDb.Execute strSQL

    End Sub


    Function fnPopul(inputQ As String) As String

    'create your string (list of values separated by comma) based on your quater
    '...
    ' the final output string has to look like this - even with commas!
    fnPopul = 12, 7
    ' so it fits into SQL statement. you will use your FOR... NEXT here
    End Function


    with this example final SQL string will look like:

    INSERT INTO tblRGAAnalysis (Quarter, Value1, Value2) Values ("Q4 2002",12,7);

    You have been so very helpful. THANK YOU!!!
    I'll let you know if it works for me.

    Have a Great Day!

Posting Permissions

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