Using array in VBA for store the queries

[ice051505]

Hi All, 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!

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

the first sql is to check the 4th columns from the initial query, for any data not contain "NA" in the 4th column, it will be added to the new query,
the second sql is for any data = "NA" in the 4th column of the initial query and "B2" in the third column, first step is to check if there is any "B2" in the entire 3rd column, if there the count of "B2" in the 3rd column is larger then 1 (eg.2), change the entire 4th column to the 3rd column. If there is only one "B2" in the 3rd column (eg.1), then copy "B2" into column 4,5,6 in the same row only.
Here is the examples:
Initial query (eg.1)
c1 c2 c3 c4 c5 c6
1 A1 B1 C1 D1 E1
2 A2 B2 NA 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 B2 B2 B2
3 A3 B3 C3 D3 D3
...

Initial query (eg.2)
c1 c2 c3 c4 c5 c6
1 A1 B1 C1 D1 E1
2 A2 B2 NA NA NA
3 A3 B2 C3 D3 NA
.....

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


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 anyone can give me some direction for how to develop an array to pass these sqls in VBA? [highlight red]Or[/highlight] any other efficient way would work in my case? Thanks!

 

[SkipVought]

array table"

What is an array table?

I can see storing SQL in a table. SOP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

I assume this is for tracking scores in Calvinball? Or at least your description reminds me of it.

http://www.bartel.org/calvinball/

http://calvinandhobbes.wikia.com/wiki/Calvinball

If not can you explain a generic set of rules if they exist? Writes the rules/steps as a clear list/bullets, not in paragraph form. The example just shows random acts, and does not explain what the intent is.

The score is still Q to 12!

 

[ice051505]

hahaha, Sorry I am new to the VBA programming, so I just want a store sql or query in VBA by sort of array thing, instead of create a bench of query in my database, since the initial query size is undefined (base on the selection). So I intend to creat first sql on VBA, and I will use my code to modify the "NA"s and at last I will pass the result query to a list box in my form.
The column in initial query is acturely the divide of areas, so column one is the area ID, and column 2 is the biggest area (area zone), and column 3 is medium area, col 4 is small area, col 5 is smaller area, col 6 is smallest area. So the initial query has been select from bench of restrict like time, species, count number. And the new query is the area level detail. I hope this will make my example more clear. Thanks!

 

[ice051505]

Hi, sorry for the confuse, what I acturely mean is, Can anyone tell me how to write code to built a loop? Since I think I will need to develop a array for check each column, start from column4 to column 6. I think the loop may be like:

With rst
        .MoveLast
        .MoveFirst
        If RecordCount > 0 Then
           For i = LBound(column4, TotalRecordNumber) To UBound(column6,TotalRecordNumber)
           Next i
        End If
End With

In the loop, how can I make it to search the column4 to column5 and define the record number, since the record number depends on the selection of the initial Query? I really need someone to help me, Please!