We help IT Professionals succeed at work.

on
Medium Priority
234 Views
I would like to shadow an array using transcode

for each array listed below the trans code will be different, therefore when the values are inserted I need the transcode to be unique to the array value.  How would I write this procedure

Dim Amount(3)

amount(0) = "<<chargeamount1>>"
amount(1) = "<<chargeamount2>>"
amount(2) = "<<chargeamount3>>"
amount(3)= "<<chargeamount3>>"

For Each x In amount

sql = "insert into " & table & " ( TRANS_CODE, ACCOUNT, AMOUNT, PRIN) & _
"values ('" & TransCode & ",'" & Account & "'," & x & ",'"   & Prin & "') "

Call WriteLog("sql = " & sql)

Call WriteLog("sql = " & sql)

Next 'x
Tags:
Comment
Watch Question

## View Solution Only

Commented:
Im not sure if this is what you mean, but instead of using a for each loop try using this:
for loopCnt = 0 to 3
'do you sql here
next

This way the loopCnt will be unique for each sql statement.

If this isnt what you mean could you clarify what is stored in the array ? do you want to put a value from the array into the sql ? or do you just need the trancode value to be unique (in which case use the loopCnt  variable as shown above)

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
Hi,

The code above works as expected.  dollar amounts are stored in the array.

'here is the insert statement. Currently the transcode is hardcoded in the script but I need it to be unique to the array. for example  chargeamount1  transcode is " 25", Chargeamount2 transcode  is "30"
so I think I would need a second array
Dim Amount(3)
amount(0) = "<<chargeamount1>>"
amount(1) = "<<chargeamount2>>"
amount(2) = "<<chargeamount3>>"
amount(3)= "<<chargeamount3>>"

For Each x In amount

sql = "insert into " & table & " ( TRANS_CODE, ACCOUNT, AMOUNT, PRIN) & _
"values ('" & TransCode & ",'" & Account & "'," & x & ",'"   & Prin & "') "

Call WriteLog("sql = " & sql)

Call WriteLog("sql = " & sql)

Commented:
Hi I think you need a more complex data structure to store this info, or some way to calculate the TransCode.

Try putting an array within your ammount array.

ie.
dim currRecord(3) '4 fields
currRecord(0) = "\$1.00"
currRecord(1) = "Product 1"
currRecord(2) = "Product Description"
currRecord(3) = "123" 'id

dim Records(4) '5 records each holding the 4 fields defined above.
Records(0) = currRecord
etc.

This way you can access the data you need by indexing the fields within the Records array.
ie.
strPrice = Records(0)(0)
strProductName = Records(0)(1)
strProductDesc = Records(0)(2)
etc etc.
'
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile