save query as array table

[ice051505]

Hi, I am working on write Sql in VBA and I want to save this query into a array table. Since I will change my Sql later,so the array size can not be defined now. I haven't done any code with array before, can anyone give me some example or hints on how to do that? Thanks!

 

[pbaldy]

What is an array table, and what makes you think you need one? What is the actual problem you're trying to solve?

 

[ice051505]

Hi baldy, Thank you for your reply.

So an example of my problem is that I have an initial table with 6 columns, and I need to develop a new query which contains the first 3 columns from the initial table, and the rest field is depend on my sql.

the first sql is to check the 4th columns from the initial table, for any data not contain "NA" in the 4th column, it will be added to the new query, for any data = "NA" in the 4th column of the initial table, it will be changed the data equal to the 3rd column of the inital table.

Here is the example:
Initial table
c1 c2 c3 c4 c5 c6
1 A1 B1 C1 D1 E1
2 A2 B2 C2 NA NA
3 A3 B3 C3 D3 NA
.....

New Query
c1 c2 c3 c4 c5 c6
1 A1 B1 C1 D1 E1
2 A2 B2 C2 C2 C2
3 A3 B3 C3 D3 D3
...

So I need to develop a loop for check all the rest columns in the original table, and my supervisor told me to do this by using array, so I just wandering if you can give me some direction for how to develop an array to pass the sql in VBA? Thanks!

What is an array table, and what makes you think you need one? What is the actual problem you're trying to solve?

 

[pbaldy]

I don't see how an array helps you. I'd use a public function or a formula for the last 2 columns. At its simplest:

IIf(C4 = "NA", C3, C4)

 

[ice051505]

Thank you for the iif function, but in my case, I need to modify the query later, since I have a very large database, lots of "NA" in the data, I ll need to make sure if one row in col 4 is "NA" and col3 is c5, then there is no data selection in col3 has the same value c5, otherwise, all the value in col 4 ll need to change to the value in col 3. There require many change in the same query, that's why I need to use array instead of the real query. Thanks!

I don't see how an array helps you. I'd use a public function or a formula for the last 2 columns. At its simplest:

IIf(C4 = "NA", C3, C4)

 

[ice051505]

Sorry for the confusion, I think I should make my case more clear,

Since the column change I made is from col3 to col5, so I want to develop a loop such as :

[FONT=Courier New]With rst
        .MoveLast
        .MoveFirst
        lRecordCount = .RecordCount
        If lRecordCount > 0 Then
            vResults = .GetRows(lRecordCount)
            For i = col3 to col5                [/FONT]
[FONT=Courier New]       For j = CountRecord.Initial Query[/FONT]
[FONT=Courier New]                Next j
            Next i
        End If
    End With[/FONT]

Then is will check each record for "NA" trough a loop, but how can I update record in the loop like the update query?
I have part of my code below, and I have no idea how to insert the loop

Set db = CurrentDb()
Sql1 = "SELECT [col_4],[col_1]From[Initial_Query] WHERE ([col_4]<>'NA');"
Set rs = db.OpenRecordset(Sql1, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql1 = "Update [New_Query]SET[New_Query].[col_4]=" & Sql1 & "WHERE [Initial_Query].[col_1]=[New_Query].[col_1];"
db.Execute Sql1, dbfilonerror
End If
rs.Close
Sql2 = "Select[col_4],[col_3],[col_1]FROM [Initial_Query]WHERE([col_4]='NA');"
Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql3="SELECT Count(Initial_Query.col_3) AS CountOfcol_3, New_Query.col3" & _
     "FROM New_Query INNER JOIN Initial_Query ON New_Query.col_1= Initial_Query.col_1" & _
     "GROUP BY New_Query.col3;"
db.Execute Sql2, dbfilonerror
   For rs.CountOfcol_3 = 1 Then
      Set rs = db.OpenRecordset(Sql2,dbOpenSnapshot)
      Sql2 = "Update [New_Query]SET[New_Query].[col_4]="& Sql2 & "WHERE [Initial_Query].[col_1]=[New_Query].[col_1];"
      db.Execute Sql2, dbfilonerror
   For rs.CountOfcol_3 <> 1 Then
      Ser rs = db.OpenRecordset(Sql2,dbOpenSnapshot)
      Sql2 = ""Update [New_Query]SET[New_Query].[col_4]=[New_Query].[col_3] WHERE [Initial_Query].[col_1]=[New_Query].[col_1];"
      db.Exeute Sql2, dbfilonerror
End If

The code is the check and update for col_4, how can I continue to check col_5 instead of rewrite the whole bench code above? Please Help Me, I am despered...

I don't see how an array helps you. I'd use a public function or a formula for the last 2 columns. At its simplest:

IIf(C4 = "NA", C3, C4)