Help required looping through an array

N

Norman Fritag

Hi there,
718.03, 414.32 __ 1020.83, 1976.1, 352.5, 947, 718.03, 366.98

Their IDs were as
----------------------------------------------

__ 508671, 508789, 508850, 513108, 514552 __ 507960, 509289, 509149, 511454,
512759__ 508671, 507960, 510436, 509149, 511454, 513633 <<<

The above sequence of Id numbers is the result of a search to find the
amount eg: 5813.95 matching to 5 details as listed above. The Amounts are
separated by comma, though I can use them later in a query to append them to
the table combinations.

The Search has in this example found 3 different type of matching amounts
and ID to the figure Eg: 5813.95 and I want to record each matched
combination separately to the table combinations.

Below is the how I have started to code to handle that situation, but I am
not very knowledge with arrays and need some help here!

Any help is much appreciated and many thanks in advance!

Regards Norman

'******snipple start *************************

arrMatches = Split(sByRefListOfUniqueIdsThatMatched, " __ ") ' this splits
the array

For intx = 0 To UBound(arrMatches) ' ???? am I going wrong here?

'How can do I loop through the array , though I get :

' if intx = 1 then i want to get "508671, 508789, 508850, 513108, 514552 "
and add it to the table combinations

' if intx = 2 then i want to get "507960, 509289, 509149, 511454, 512759"
and add it to the table combinations

' if intx = 3 then i want to get "508671, 507960, 510436, 509149, 511454,
513633" and add it to the table combinations

sEditTheseIDs = ??????

intcnt = DMax("Number", "Combinations") ' get the last CombinationID

If intcnt <> 0 Then intcnt = intcnt + 1 ' increment *1

' add one set of matched ids to the table combinations

strsql = "INSERT INTO Combinations ([Number],Combination, Matchamt,
Filedate, EmployerName, FlagToBePosted_YN, JnlId, JnlSplitId,
OutstandingAmount, batchid, IntendPayHow )" _

& " SELECT " & intcnt & " AS [Number], " & dbTheDesiredSum & " as
[Matchamt], " & UBound(arrMatches) & " as [Combination], Batch.Filedate,
Batch.EmployerName," _

& " BatchAmount.FlagToBePosted_YN,
BatchAmount.JnlId,BatchAmount.JnlSplitId," _

& " BatchAmount.OutstandingAmount, BatchAmount.BatchId,Batch.IntendPayHow" _

& " FROM BatchAmount INNER JOIN Batch ON BatchAmount.BatchId =Batch.BatchId"
_

& " WHERE (((BatchAmount.BatchId) In(" & sMess & ") AND
((Batch.IntendPayHow)='" & strpaytype & "')));"

'strsql = "Update BatchAmount INNER JOIN Batch ON BatchAmount.BatchId =
Batch.BatchId" _

& " Set FlagToBePosted_YN = -1 where (((BatchAmount.BatchID) IN (" &
Replace(sEditTheseIDs, " __ ", ", ") & ")) AND

((Batch.IntendPayHow)='DDE'))"

CurrentDb.Execute (strsql)

DoEvents

Next intx

'********************************end**************************
 
M

Martin

Just a thought but your Split function is producing a one-dimensional array
with each element containing a string with five numbers divided by commas,
i.e. arrMatches(0) will be the first set of five numbers, arrMatches(1) will
the second and so on. You're going to need to split each of these further if
you want to use the numbers separately - perhaps another Split function with
comma as the delimiter?

Norman Fritag said:
Hi there,
718.03, 414.32 __ 1020.83, 1976.1, 352.5, 947, 718.03, 366.98

Their IDs were as
----------------------------------------------

__ 508671, 508789, 508850, 513108, 514552 __ 507960, 509289, 509149, 511454,
512759__ 508671, 507960, 510436, 509149, 511454, 513633 <<<

The above sequence of Id numbers is the result of a search to find the
amount eg: 5813.95 matching to 5 details as listed above. The Amounts are
separated by comma, though I can use them later in a query to append them to
the table combinations.

The Search has in this example found 3 different type of matching amounts
and ID to the figure Eg: 5813.95 and I want to record each matched
combination separately to the table combinations.

Below is the how I have started to code to handle that situation, but I am
not very knowledge with arrays and need some help here!

Any help is much appreciated and many thanks in advance!

Regards Norman

'******snipple start *************************

arrMatches = Split(sByRefListOfUniqueIdsThatMatched, " __ ") ' this splits
the array

For intx = 0 To UBound(arrMatches) ' ???? am I going wrong here?

'How can do I loop through the array , though I get :

' if intx = 1 then i want to get "508671, 508789, 508850, 513108, 514552 "
and add it to the table combinations

' if intx = 2 then i want to get "507960, 509289, 509149, 511454, 512759"
and add it to the table combinations

' if intx = 3 then i want to get "508671, 507960, 510436, 509149, 511454,
513633" and add it to the table combinations

sEditTheseIDs = ??????

intcnt = DMax("Number", "Combinations") ' get the last CombinationID

If intcnt <> 0 Then intcnt = intcnt + 1 ' increment *1

' add one set of matched ids to the table combinations

strsql = "INSERT INTO Combinations ([Number],Combination, Matchamt,
Filedate, EmployerName, FlagToBePosted_YN, JnlId, JnlSplitId,
OutstandingAmount, batchid, IntendPayHow )" _

& " SELECT " & intcnt & " AS [Number], " & dbTheDesiredSum & " as
[Matchamt], " & UBound(arrMatches) & " as [Combination], Batch.Filedate,
Batch.EmployerName," _

& " BatchAmount.FlagToBePosted_YN,
BatchAmount.JnlId,BatchAmount.JnlSplitId," _

& " BatchAmount.OutstandingAmount, BatchAmount.BatchId,Batch.IntendPayHow" _

& " FROM BatchAmount INNER JOIN Batch ON BatchAmount.BatchId =Batch.BatchId"
_

& " WHERE (((BatchAmount.BatchId) In(" & sMess & ") AND
((Batch.IntendPayHow)='" & strpaytype & "')));"

'strsql = "Update BatchAmount INNER JOIN Batch ON BatchAmount.BatchId =
Batch.BatchId" _

& " Set FlagToBePosted_YN = -1 where (((BatchAmount.BatchID) IN (" &
Replace(sEditTheseIDs, " __ ", ", ") & ")) AND

((Batch.IntendPayHow)='DDE'))"

CurrentDb.Execute (strsql)

DoEvents

Next intx

'********************************end**************************
Click to expand...
 
Top