454,968 Members | 1,180 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

# help with looping through array required!

 P: n/a Hi there, __ 1020.83, 2305.22, 1176.86, 755.12, 123.41 __ 1976.1, 1325.99, 947, 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************** ************ Mar 2 '06 #1
4 Replies

 P: n/a "Norman Fritag" wrote in message news:44***********************@per-qv1-newsreader-01.iinet.net.au... Hi there,__ 1020.83, 2305.22, 1176.86, 755.12, 123.41 __ 1976.1, 1325.99, 947, 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************** ************ Norman Is this somehow an extension to the question which was posted on 22 Feb? I seem to remember writing a complete and hopefully working solution to the problem posed for which there was zero feedback one way or the other. I don't even know if you read it. While people who volunteer answers here do so for free, some feedback is appreciated - especially if you are looking for further help from the group. On a similar note, you seem to be multi-posting to "microsoft.public.access.modulesdaovba" which is another practice likely to annoy any potential helpers. Mar 2 '06 #2