Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array - retrieve certain amount of recods for query
Hi all.
I'm trying to use for my first time, a single dimension array to use within an external sql query. I build the array from column A. This can be a variable amount, from 2 to 800. However, I can only use 150 rows at a time in the query. Below is what I have so far, in building the array. It's just the pulling out of 150 records at a time, or the remainder to put into the sql query. I will be building the sql query string as such: bug_number = (bug1,bug2,bug3) --these are the numbers in the array. (must have comma's between them) strQuery_main = "select distinct(bug_number) from applied_bugs" I'm unsure on how to begin pulling out the records in the array. i.e. start off with a Do Loop then use a For Next statement, and how to actually keep track of where I am at. The query return will be put into an array also. Any assistance would be greatly appreciated. Cheers, Dougy --------------------- Sub QueryBugsProcedure 'Declare variables Dim rngList As Range Dim intMax As Integer Dim rwCnt As Integer Dim intArray As Integer Dim Arr() As Long Dim ArrResults() As Long 'Number of bugs to be included in each sql query (max 250) intMax = 150 'Count number of bugs Range("A1").Activate If ActiveCell.Offset(1, 0) = Empty Then rwCnt = 1 Else Range(Selection, Selection.End(xlDown)).Select rwCnt = Selection.Count End If 'Create array ReDim Arr(1 To rwCnt) For Each c In Selection If c.Value < Empty Then i = i + 1 Arr(i) = c.Value End If Next c 'Move to this cell for query return Range("B12").Activate 'count records in array checkpoint Debug.Print UBound(Arr) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array - retrieve certain amount of recods for query | Excel Programming | |||
Retrieve result of query from Access | Excel Programming | |||
How to retrieve last date and amount from columns? | Excel Worksheet Functions | |||
write a query to retrieve data | New Users to Excel | |||
Grabbing recods based on date and shift values | Excel Worksheet Functions |