By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
4 Replies


P: n/a

"Norman Fritag" <mu*****@ozemail.com.au> 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

P: n/a
Anthony,
yes this is a follow-up question on what was posted on 22 Feb!
The suggestion provided where not giving me a solution, although it
inspirited me to think further.
I found a solution in the meantime. The code may me shocking but it works. I
have been able to modify the code to run faster as well, but arrays are not
my strength.
Yes, I appreciate what this group had offered and thank everyone for there
input!

Regards

Norman

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"Norman Fritag" <mu*****@ozemail.com.au> 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 #3

P: n/a

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Anthony,
yes this is a follow-up question on what was posted on 22 Feb!
The suggestion provided where not giving me a solution, although it
inspirited me to think further.
I found a solution in the meantime. The code may me shocking but it works.
I
have been able to modify the code to run faster as well, but arrays are
not
my strength.
Yes, I appreciate what this group had offered and thank everyone for there
input!

Regards

Norman

Hi Norman
I am sorry to hear you could not get my suggestion to work, but would be
interested to see if you really do have a working alternative - especially
if you have managed to get the code to run faster.
The original challenge was to find which combinations picked from a choice
of 20 numbers would sum together to make 275. Given that:

strSeries = "10,2,4,17,24,5,30,40,50," & _
"100,23,35,200,3501,201," & _
"245,323,2000,33,44"
I found 103 matches in 5.89 seconds.

And if I bothered to sort this list first - putting the largest number
first:

strSeries = "3501,2000,323,245,201," & _
"200,100,50,44,40,35,33," & _
"30,24,23,17,10,5,4,2"
I could dramatically reduce the time to 103 matches in 1.72 seconds

Please would you show us:
How many matches you find in what time
A list of them, eg:
275 = 201 + 44 + 30
275 = 245 + 30
275 = 200 + 40 + 35
And the code you used.
I may be able to look at the latest question some time tomorrow, but because
you have included so many lines of code some of which is to do with the
splitting and some of which covers the creating of new records, it is hard
to see exactly the bit you are having trouble with. Ideally the question
would focus on a very specific point.
As to the bit about posting to multiple newsgroups, the point is you should
(if you have to) cross-post rather than multi-post. If you cross-post,
everyone can see what is happening and who is providing answers - but if you
multi-post, you are effectively asking separate groups of people to
independently work on the problem without them knowing their efforts are
being duplicated in other newsgroups (we are all real people with real jobs,
wives, kids, household chores, etc to fit in around the newsgroups!)
Mar 2 '06 #4

P: n/a
Anthony,

Don't look at my latest question. I have resolved it. It was so simple I
just was tired!
In a matter of a fact I haven't tried your code, as I didn't knew that it
was there! silly me!
I will however check it out. let you know how I am going with this.

thanks again
regards

Norman

"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Anthony,
yes this is a follow-up question on what was posted on 22 Feb!
The suggestion provided where not giving me a solution, although it
inspirited me to think further.
I found a solution in the meantime. The code may me shocking but it works. I
have been able to modify the code to run faster as well, but arrays are
not
my strength.
Yes, I appreciate what this group had offered and thank everyone for there input!

Regards

Norman

Hi Norman
I am sorry to hear you could not get my suggestion to work, but would be
interested to see if you really do have a working alternative - especially
if you have managed to get the code to run faster.
The original challenge was to find which combinations picked from a choice
of 20 numbers would sum together to make 275. Given that:

strSeries = "10,2,4,17,24,5,30,40,50," & _
"100,23,35,200,3501,201," & _
"245,323,2000,33,44"
I found 103 matches in 5.89 seconds.

And if I bothered to sort this list first - putting the largest number
first:

strSeries = "3501,2000,323,245,201," & _
"200,100,50,44,40,35,33," & _
"30,24,23,17,10,5,4,2"
I could dramatically reduce the time to 103 matches in 1.72 seconds

Please would you show us:
How many matches you find in what time
A list of them, eg:
275 = 201 + 44 + 30
275 = 245 + 30
275 = 200 + 40 + 35
And the code you used.
I may be able to look at the latest question some time tomorrow, but

because you have included so many lines of code some of which is to do with the
splitting and some of which covers the creating of new records, it is hard
to see exactly the bit you are having trouble with. Ideally the question
would focus on a very specific point.
As to the bit about posting to multiple newsgroups, the point is you should (if you have to) cross-post rather than multi-post. If you cross-post,
everyone can see what is happening and who is providing answers - but if you multi-post, you are effectively asking separate groups of people to
independently work on the problem without them knowing their efforts are
being duplicated in other newsgroups (we are all real people with real jobs, wives, kids, household chores, etc to fit in around the newsgroups!)

Mar 3 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.