Subscript Error

[Rexolio]

Below is the actual script I am using. I keep getting a Subscript error.

sqlstr = "SELECT * FROM my_table WHERE inputdate = " & sInDate
Set rs = Conn.Execute(sqlstr)

If rs.EOF Then
booData = False
Else
booData = True
arrNames = rs.GetRows()

for i = 0 to ubound(arrnames,2)
if i mod 3 = 0 then
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1) & vbtab & Arrnames(1,i+2) <-- error on this line
output = output & Arrnames(2,i) & vbtab & Arrnames(2,i+1) & vbtab & Arrnames(2,i+2)
output = ...and on until the last field
elseif (i + 1) mod 3 = 0 then
output = Arrnames(1,i)
output = output & Arrnames(2,i)
output = ...and on
else
output = Arrnames(1,i) & vbtab & Arrnames(1,i+1)
output = output & Arrnames(2,i) & vbtab & Arrnames(2,i+1)
output = ...and on
end if
next
end if

I'm getting a Subscript out of range: '[number: 47]' on the very first line that begins with &quot;output&quot;. 47 is the number of records for the specified criteria in my database. The only thing I haven't entered here is:

1 = name
2 = address
3 = city

that someone told be to do. I've tried a couple of different placements and variations of this, but I get errors on those lines. HELP!

 

[link9]

If the number of records is 47, then your 0-based array will only go up to 46, hence the &quot;out of bounds&quot; error.

Try this for your loop:

for i = 0 to ubound(arrnames,2) - 1


Paul Prewett

 

[Rexolio]

still getting the same message

 

[JohnYingling]

Link9
UBound is already 1 less than the count. An array with UBound=0 has 1 item.

Rexoilio.
If I = 45 then I + 2 = 47 is outside the Ubound = 46 for 47 items.
Do you mean 47 items or UBOUND = 47 which is 48 items?

http://www.VBCompare.com

http://www.VBSortGen.com

 

[Rexolio]

Okay, thanks John, but you confused me even more.

The script is working now, but what I assumed it would do is return the first record-name, second record-name, third record-name, then first record-address, second record-address, third record-address, then first record-city....

then loop and start again with record number 4, continuing in groups of 3 (4, 5, 6), and then loop yet again on and on until the last record.

it doesn't...it stops at 3...no looping.

help!

 

[JohnYingling]

I can't &quot;picture&quot; wa=hat you are trying to do.

for i = 0 to ubound(arrnames,2)
  if i mod 3 = 0 then 
  ' I = 0  3  6  9 12 15 18 21 24 27
  elseif (i + 1) mod 3 = 0 then  
  ' I = 1  4  7 10 13 16 17 24 27  
  else
  ' I = 2  5  8 11 14 17 18 25 28  
  end if
next

Do you want Step 3.
for i = 0 to ubound(arrnames,2) Step 3

http://www.VBCompare.com

http://www.VBSortGen.com

 

[JohnYingling]

for i = 0 to ubound(arrnames,2)
  ' I = 0  3  6  9 12 15 18 21 24 27
  next
for i = I to ubound(arrnames,2) Step 3
  if (i) mod 3 = 1 then  
  ' I = 1  4  7 10 13 16 17 24 27  
  else
  ' I = 2  5  8 11 14 17 18 25 28  
  end if
next

http://www.VBCompare.com

http://www.VBSortGen.com

 

[Rexolio]

Here's what I have to do. Don't know if its going to be too long, but I'm going to try to illustrate it best I can.

1. Database has Customer contact information: name, address, city, state & zip

2. Am using a program that prints special mailing labels and reports from web browser.

3. I cannot do a simple array where you merely set out a table with x number of rows and 3 columns. I've done that and know how to do it. Once you identify, as you illustrated above, which record is the 3rd record of each row, you simple start a new row.

4. I have to pull the records and integrate them with the scripting of the program. I'm prevented from doing the above because you must print everything that will go on the 1st line at once, close it, then move to the 2nd line, and on and on. The problem with this is that on the first line, you'll have the 3 names from 3 different records. Then close the line, start a new one, and have the street addresses of the same 3 records, close the line, start new line, city/state/zip of the those 3 records, close the line and start all over again with the next 3 records. A traditional loop is prevented here.

I need help being able to pull just the name from record one, then just the name from record 2, then just the name from record 3. THEN, just the address from record 1, then the address of record 2....you get the picture. THEN, after the 3rd record, I'll need to group the next 3 records and start over.

THANK YOU SO MUCH FOR YOUR TIME!

 

[JohnYingling]

Yeah, It looks like 3-UP telephone book style. The multi-dimensional array is giving me a headache.

http://www.VBCompare.com

http://www.VBSortGen.com