Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11018 > unrolled thread

help with array code

Back to article view | Back to comp.databases.ms-access

Contents

  help with array code bobh <vulcaned@yahoo.com> - 2015-08-20 08:43 -0700
    Re: help with array code Ulrich Möller <knobbi38@arcor.de> - 2015-08-20 19:40 +0200
      Re: help with array code Ulrich Möller <knobbi38@arcor.de> - 2015-08-21 11:39 +0200
    Re: help with array code Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-08-20 10:53 -0700
      Re: help with array code bobh <vulcaned@yahoo.com> - 2015-08-21 06:16 -0700
        Re: help with array code Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-08-21 10:37 -0700
          Re: help with array code "Phil" <phil@stantonfamily.co.uk> - 2015-08-22 00:32 +0100
            Re: help with array code "Phil" <phil@stantonfamily.co.uk> - 2015-08-22 10:08 +0100
    Re: help with array code bobh <vulcaned@yahoo.com> - 2015-08-24 07:53 -0700
      Re: help with array code Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-08-24 09:38 -0700

#11018 — help with array code

Hi All,

I'm not getting it I searched around put I didn't find anything specific to my need so

I have a multi-value field which I am trying to determine in what position "1" is
I thought this would doo it but it does not work I get a subscript out of range error;

Dim MyArray, strPosCnt
MyArray = Array(MyRec!Priority)
Do Until MyRec.EOF
For i = 0 To UBound(MyArray)
If MyArray(i) = "1" Then
strPosCnt = i
Exit For
End If
Next i

with the Priority field containing values
Priority field
2,1,3,4
1,4,3,2
9,6,1
1
3,7,4,1

I would want strPosCnt to be
2
1
3
1
4

any help with the code is greatly appreciated.
bobh

[toc] | [next] | [standalone]

#11019

Am 20.08.2015 um 17:43 schrieb bobh:
> Hi All,
>
> I'm not getting it I searched around put I didn't find anything specific to my need so
>
> I have a multi-value field which I am trying to determine in what position "1" is
> I thought this would doo it but it does not work I get a subscript out of range error;
>
> Dim MyArray, strPosCnt
> MyArray = Array(MyRec!Priority)
> Do Until MyRec.EOF
> For i = 0 To UBound(MyArray)
> If MyArray(i) = "1" Then
> strPosCnt = i
> Exit For
> End If
> Next i
>
> with the Priority field containing values
> Priority field
> 2,1,3,4
> 1,4,3,2
> 9,6,1
> 1
> 3,7,4,1
>
> I would want strPosCnt to be
> 2
> 1
> 3
> 1
> 4
>
> any help with the code is greatly appreciated.
> bobh
MVFs are  represented as a recordset instead of an array.
To get the "inner" recordset with the mvf values:

set rstMVF = MyRec!Priority.Value
do until rstMVF.EOF
     ' something todo
loop

You can handle the MVF-Recordset in the same manner as usual recordsets.

Ulrich

[toc] | [prev] | [next] | [standalone]

#11024

Am 20.08.2015 um 19:40 schrieb Ulrich Möller:
> Am 20.08.2015 um 17:43 schrieb bobh:
>> Hi All,
>>
>> I'm not getting it I searched around put I didn't find anything 
>> specific to my need so
>>
>> I have a multi-value field which I am trying to determine in what 
>> position "1" is
>> I thought this would doo it but it does not work I get a subscript 
>> out of range error;
>>
>> Dim MyArray, strPosCnt
>> MyArray = Array(MyRec!Priority)
>> Do Until MyRec.EOF
>> For i = 0 To UBound(MyArray)
>> If MyArray(i) = "1" Then
>> strPosCnt = i
>> Exit For
>> End If
>> Next i
>>
>> with the Priority field containing values
>> Priority field
>> 2,1,3,4
>> 1,4,3,2
>> 9,6,1
>> 1
>> 3,7,4,1
>>
>> I would want strPosCnt to be
>> 2
>> 1
>> 3
>> 1
>> 4
>>
>> any help with the code is greatly appreciated.
>> bobh
> MVFs are  represented as a recordset instead of an array.
> To get the "inner" recordset with the mvf values:
>
> set rstMVF = MyRec!Priority.Value
> do until rstMVF.EOF
>     ' something todo
> loop
>
> You can handle the MVF-Recordset in the same manner as usual recordsets.
>
> Ulrich
>
>
Hi Bobh,

forget my posting. I was thinking of a real MVF-Field but you asked 
about a field with combined values. That's a completely different topic.

Ulrich

[toc] | [prev] | [next] | [standalone]

#11020

On Thursday, August 20, 2015 at 11:43:13 AM UTC-4, bobh wrote:
> Hi All,
> 
> I'm not getting it I searched around put I didn't find anything specific to my need so
> 
> I have a multi-value field which I am trying to determine in what position "1" is
> I thought this would doo it but it does not work I get a subscript out of range error;
> 
> Dim MyArray, strPosCnt
> MyArray = Array(MyRec!Priority)
> Do Until MyRec.EOF
> For i = 0 To UBound(MyArray)
> If MyArray(i) = "1" Then
> strPosCnt = i
> Exit For
> End If
> Next i
> 
> with the Priority field containing values
> Priority field
> 2,1,3,4
> 1,4,3,2
> 9,6,1
> 1
> 3,7,4,1
> 
> I would want strPosCnt to be
> 2
> 1
> 3
> 1
> 4
> 
> any help with the code is greatly appreciated.
> bobh

Bob, I think it would be better if you used Split() instead of Array()

This works.

Public Sub TA()
Dim A As Variant
Dim B As Variant
Dim c As Variant
c = "1,2,3,4,5"
A = Split(c, ",")
B = A(2)
Debug.Print B
End Sub

I assume you were cutting and pasting as you have a Do While loop but never a move to the next record, assigning the value outside of the do loop, and Endif but no If.  

[toc] | [prev] | [next] | [standalone]

#11027

On Thursday, August 20, 2015 at 1:53:55 PM UTC-4, Patrick Finucane wrote:
> On Thursday, August 20, 2015 at 11:43:13 AM UTC-4, bobh wrote:
> > Hi All,
> > 
> > I'm not getting it I searched around put I didn't find anything specific to my need so
> > 
> > I have a multi-value field which I am trying to determine in what position "1" is
> > I thought this would doo it but it does not work I get a subscript out of range error;
> > 
> > Dim MyArray, strPosCnt
> > MyArray = Array(MyRec!Priority)
> > Do Until MyRec.EOF
> > For i = 0 To UBound(MyArray)
> > If MyArray(i) = "1" Then
> > strPosCnt = i
> > Exit For
> > End If
> > Next i
> > 
> > with the Priority field containing values
> > Priority field
> > 2,1,3,4
> > 1,4,3,2
> > 9,6,1
> > 1
> > 3,7,4,1
> > 
> > I would want strPosCnt to be
> > 2
> > 1
> > 3
> > 1
> > 4
> > 
> > any help with the code is greatly appreciated.
> > bobh
> 
> Bob, I think it would be better if you used Split() instead of Array()
> 
> This works.
> 
> Public Sub TA()
> Dim A As Variant
> Dim B As Variant
> Dim c As Variant
> c = "1,2,3,4,5"
> A = Split(c, ",")
> B = A(2)
> Debug.Print B
> End Sub
> 
> I assume you were cutting and pasting as you have a Do While loop but never a move to the next record, assigning the value outside of the do loop, and Endif but no If.

I did not paste the entire code as I was trying to get the array part working but you are correct in that I do have code to loop thru the records and update a field with the resultant value.

Split does not fit what I'm trying to do, I guess I didn't explain it well so let me try again.

I have a table in it the field named Priority has multiple values in it and I need to capture the index number of what position the number = '1' is in.
example;
Priority field     index position of the number = '1'
2,1,3,4               2 -> the number '1' is in the second position
1,4,3,2               1 -> the number '1' is in the first position
9,6,1                 3 -> the number '1' is in the third position
1                     1 -> the number '1' is in the first position
3,7,4,1               4 -> the number '1' is in the fourth position

bobh.

[toc] | [prev] | [next] | [standalone]

#11028

On Friday, August 21, 2015 at 9:16:34 AM UTC-4, bobh wrote:
> On Thursday, August 20, 2015 at 1:53:55 PM UTC-4, Patrick Finucane wrote:
> > On Thursday, August 20, 2015 at 11:43:13 AM UTC-4, bobh wrote:
> > > Hi All,
> > > 
> > > I'm not getting it I searched around put I didn't find anything specific to my need so
> > > 
> > > I have a multi-value field which I am trying to determine in what position "1" is
> > > I thought this would doo it but it does not work I get a subscript out of range error;
> > > 
> > > Dim MyArray, strPosCnt
> > > MyArray = Array(MyRec!Priority)
> > > Do Until MyRec.EOF
> > > For i = 0 To UBound(MyArray)
> > > If MyArray(i) = "1" Then
> > > strPosCnt = i
> > > Exit For
> > > End If
> > > Next i
> > > 
> > > with the Priority field containing values
> > > Priority field
> > > 2,1,3,4
> > > 1,4,3,2
> > > 9,6,1
> > > 1
> > > 3,7,4,1
> > > 
> > > I would want strPosCnt to be
> > > 2
> > > 1
> > > 3
> > > 1
> > > 4
> > > 
> > > any help with the code is greatly appreciated.
> > > bobh
> > 
> > Bob, I think it would be better if you used Split() instead of Array()
> > 
> > This works.
> > 
> > Public Sub TA()
> > Dim A As Variant
> > Dim B As Variant
> > Dim c As Variant
> > c = "1,2,3,4,5"
> > A = Split(c, ",")
> > B = A(2)
> > Debug.Print B
> > End Sub
> > 
> > I assume you were cutting and pasting as you have a Do While loop but never a move to the next record, assigning the value outside of the do loop, and Endif but no If.
> 
> I did not paste the entire code as I was trying to get the array part working but you are correct in that I do have code to loop thru the records and update a field with the resultant value.
> 
> Split does not fit what I'm trying to do, I guess I didn't explain it well so let me try again.
> 
> I have a table in it the field named Priority has multiple values in it and I need to capture the index number of what position the number = '1' is in.
> example;
> Priority field     index position of the number = '1'
> 2,1,3,4               2 -> the number '1' is in the second position
> 1,4,3,2               1 -> the number '1' is in the first position
> 9,6,1                 3 -> the number '1' is in the third position
> 1                     1 -> the number '1' is in the first position
> 3,7,4,1               4 -> the number '1' is in the fourth position
> 
> bobh.

Why would split not work?  It throws the values into an array.

Here's a test
x = "1,2,3,4,5"
y = array(x)
? lbound(y)
 0 
? Ubound(y)
 0 

It is not an array.

Now this
y = array(1,2,3,4,5)
? lbound(y)
 0 
? Ubound(y)
 4 

How is your value stored in rst!Priority?  As a string?  Won't work.  As a number?  What kind of number is that?  Boolean? not just 1s and 0s.  How the hell do you expect Array() to work?  By the power of prayer?

Why don't you stop your code (use STOP) and step thru the code.  See what your Ubound and Lbound values are.

Why can't you use Split()?  Why does it not fit what you are trying to do.  Up to this point, all I see is you not succeeding.

[toc] | [prev] | [next] | [standalone]

#11029

> 
> How is your value stored in rst!Priority? As a string? Won't work. As a
> number? What kind of number is that? Boolean? not just 1s and 0s. How the
> hell do you expect Array() to work? By the power of prayer?
> 
> Why don't you stop your code (use STOP) and step thru the code. See what
> your Ubound and Lbound values are.
> 
> Why can't you use Split()? Why does it not fit what you are trying to do.
> Up to this point, all I see is you not succeeding.
> 
> 
Air code

Dim i as integer, j as integer
dim a as string
dim MyDb as database
Dim Myset as recordset

set mydb = current db
set Myset = Mdb.OpenRecordset(MTable)
With MySet
do until.eof
for i = 1 to len(!Priority)
a = mid(Priority,i,1)
if a = "1" then      is it a "1"
if mid(!Priority) = "," or mid(!Priority) = vbcrlf then ' is the next symbol
a comma or end of record   Debug i
  for j = i step -1
    if mid (!Priority) = "," then      'count any preceding commas
    i = i -1
next j
debug i    ' THis should be the corret position
  exit for
next i
movenext
loop
close
set myset = nothing
end with

Phil

[toc] | [prev] | [next] | [standalone]

#11030

On 22/08/2015 00:32:58, "Phil" wrote:
> 
>>
>> How is your value stored in rst!Priority? As a string? Won't work. As a
>> number? What kind of number is that? Boolean? not just 1s and 0s. How the
>> hell do you expect Array() to work? By the power of prayer?
>>
>> Why don't you stop your code (use STOP) and step thru the code. See what
>> your Ubound and Lbound values are.
>>
>> Why can't you use Split()? Why does it not fit what you are trying to do.
>> Up to this point, all I see is you not succeeding.
>>
>>
> Air code
> 
> Dim i as integer, j as integer
> dim a as string
> dim MyDb as database
> Dim Myset as recordset
> 
> set mydb = current db
> set Myset = Mdb.OpenRecordset(MTable)
> With MySet
> do until.eof
> for i = 1 to len(!Priority)
> a = mid(Priority,i,1)
> if a = "1" then      is it a "1"
> if mid(!Priority) = "," or mid(!Priority) = vbcrlf then ' is the next
> symbol a comma or end of record   Debug i
> for j = i step -1
> if mid (!Priority) = "," then      'count any preceding commas
> i = i -1
> next j
> debug i    ' THis should be the corret position
> exit for
> next i
> movenext
> loop
> close
> set myset = nothing
> end with
> 
> Phil
> 
> 

Whoops, the line should read
For j = i to 1 Step -1

Phil

[toc] | [prev] | [next] | [standalone]

#11031

On Thursday, August 20, 2015 at 11:43:13 AM UTC-4, bobh wrote:
> Hi All,
> 
> I'm not getting it I searched around put I didn't find anything specific to my need so
> 
> I have a multi-value field which I am trying to determine in what position "1" is
> I thought this would doo it but it does not work I get a subscript out of range error;
> 
> Dim MyArray, strPosCnt
> MyArray = Array(MyRec!Priority)
> Do Until MyRec.EOF
> For i = 0 To UBound(MyArray)
> If MyArray(i) = "1" Then
> strPosCnt = i
> Exit For
> End If
> Next i
> 
> with the Priority field containing values
> Priority field
> 2,1,3,4
> 1,4,3,2
> 9,6,1
> 1
> 3,7,4,1
> 
> I would want strPosCnt to be
> 2
> 1
> 3
> 1
> 4
> 
> any help with the code is greatly appreciated.
> bobh


Thanks Patrick and Phil,  I worked on it and this is what worked

   Dim MyDb As DAO.Database, MyRec As DAO.Recordset
   Dim strPosCnt As Integer
   Dim MyArray As Variant
   Dim i As Integer
      
   Set MyDb = CurrentDb()
   Set MyRec = MyDb.OpenRecordset("tblImport", dbOpenDynaset)
   strPosCnt = 0
   MyRec.MoveLast
   MyRec.MoveFirst
   
   MyArray = Split(MyRec!Priority, ",")
   Do Until MyRec.EOF
      For i = LBound(MyArray) To UBound(MyArray)
         If MyArray(i) = "1" Then
         strPosCnt = i + 1
         Exit For
         End If
      Next i
      MyRec.Edit
      MyRec!PositionOfOne = strPosCnt
      If strPosCnt = "1" Then
         MyRec!PositionValue = MyRec!DXONE
      ElseIf strPosCnt = "2" Then
         MyRec!PositionValue = MyRec!DXTWO
      ElseIf strPosCnt = "3" Then
         MyRec!PositionValue = MyRec!DXTHREE
      ElseIf strPosCnt = "4" Then
         MyRec!PositionValue = MyRec!DXFOUR
      End If
      MyRec.Update
      MyRec.MoveNext
      MyArray = Split(MyRec!Priority, ",")
      strPosCnt = 0
   Loop
   MyRec.Close
   MyDb.Close
   MsgBox "I'm Done"

bobh.

[toc] | [prev] | [next] | [standalone]

#11032

On Monday, August 24, 2015 at 10:53:36 AM UTC-4, bobh wrote:
> On Thursday, August 20, 2015 at 11:43:13 AM UTC-4, bobh wrote:
> > Hi All,
> > 
> > I'm not getting it I searched around put I didn't find anything specific to my need so
> > 
> > I have a multi-value field which I am trying to determine in what position "1" is
> > I thought this would doo it but it does not work I get a subscript out of range error;
> > 
> > Dim MyArray, strPosCnt
> > MyArray = Array(MyRec!Priority)
> > Do Until MyRec.EOF
> > For i = 0 To UBound(MyArray)
> > If MyArray(i) = "1" Then
> > strPosCnt = i
> > Exit For
> > End If
> > Next i
> > 
> > with the Priority field containing values
> > Priority field
> > 2,1,3,4
> > 1,4,3,2
> > 9,6,1
> > 1
> > 3,7,4,1
> > 
> > I would want strPosCnt to be
> > 2
> > 1
> > 3
> > 1
> > 4
> > 
> > any help with the code is greatly appreciated.
> > bobh
> 
> 
> Thanks Patrick and Phil,  I worked on it and this is what worked
> 
>    Dim MyDb As DAO.Database, MyRec As DAO.Recordset
>    Dim strPosCnt As Integer
>    Dim MyArray As Variant
>    Dim i As Integer
>       
>    Set MyDb = CurrentDb()
>    Set MyRec = MyDb.OpenRecordset("tblImport", dbOpenDynaset)
>    strPosCnt = 0
>    MyRec.MoveLast
>    MyRec.MoveFirst
>    
>    MyArray = Split(MyRec!Priority, ",")
>    Do Until MyRec.EOF
>       For i = LBound(MyArray) To UBound(MyArray)
>          If MyArray(i) = "1" Then
>          strPosCnt = i + 1
>          Exit For
>          End If
>       Next i
>       MyRec.Edit
>       MyRec!PositionOfOne = strPosCnt
>       If strPosCnt = "1" Then
>          MyRec!PositionValue = MyRec!DXONE
>       ElseIf strPosCnt = "2" Then
>          MyRec!PositionValue = MyRec!DXTWO
>       ElseIf strPosCnt = "3" Then
>          MyRec!PositionValue = MyRec!DXTHREE
>       ElseIf strPosCnt = "4" Then
>          MyRec!PositionValue = MyRec!DXFOUR
>       End If
>       MyRec.Update
>       MyRec.MoveNext
>       MyArray = Split(MyRec!Priority, ",")
>       strPosCnt = 0
>    Loop
>    MyRec.Close
>    MyDb.Close
>    MsgBox "I'm Done"
> 
> bobh.

Glad you got it to work.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web