?
Solved

Have an array and need the unique values out of it

Posted on 2007-07-23
21
Medium Priority
?
193 Views
Last Modified: 2013-11-27
I have the below code which successfully creates an array of values from column 2 of a listview control. Now I need this array or another one to contain only the unique values from this array. Can anyone show me how to do this?

Thanks!
JA67


Dim strn As String
Dim RowCount As Integer

RowCount = Forms![frmChainOfCustody]![ListViewChainOfCust].ListItems.Count - 1

Dim i As Integer
ReDim CaseNumb(1 To RowCount) As String

For RowsCOC = 1 To Forms![frmChainOfCustody]![ListViewChainOfCust].ListItems.Count
     
  Set ItemCOC = Forms![frmChainOfCustody]![ListViewChainOfCust].ListItems(RowsCOC)
  strn = ItemCOC.SubItems(2)
 
  CaseNumb(RowCount) = Mid$(strn, 1, InStr(strn, "#") - 1)

  Next RowsCOC
0
Comment
Question by:JA67
  • 12
  • 5
  • 4
21 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 19550326
Should not ListItems be ItemData?  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19550332
BTW I'm running A2K.
0
 

Author Comment

by:JA67
ID: 19550351
GRayL -- Thanks for the response. I really don't know if ListItems should be ItemData or not. I've used it this way for a long time and it works.

I am using Access 2007.

Thanks,
JA67
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:JA67
ID: 19550375
Are you suggesting that if I change it to ItemData that I would get only unique values right from the start?

JA67
0
 

Author Comment

by:JA67
ID: 19550395
When I use ItemData, I get an error, "Item does not support this property or object..." or something like that....

0
 

Author Comment

by:JA67
ID: 19550399
I just chill out and let you respond. Sorry...
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19550450
No, I just cannot find ListItems in my Object Browser - but I see you are using A2007 - yet another one of the changes.
0
 

Author Comment

by:JA67
ID: 19550470
I have been using "ListItems" before 2007 as well.
0
 

Author Comment

by:JA67
ID: 19550904
ListItems does not show up in my Object Browser either. I had forgotten about that, but I just tested it and it does not show up, although it does work... I can remember wondering about that a long time ago when I first set it up.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 600 total points
ID: 19550947
If your code produces the array CaseNumb(), albeit with dups then

dim i as integer, j as integer, k as integer, var as variant,csvstr as variant, newarray as variant
j=ubound(CaseNumb)
for i=0 to j
if isnull(CaseNumb(i) then
  i=i+1
  Loop
End If
var = CaseNumb(i)
csvstr=csvstr & CaseNumb(i) + ","
for k=i+1 to j
  If CaseNumb(k) = Var Then
    CaseNumb(k) = null
  End If
  k+k+1
next k
'at this point we have the comma separated variable string csvstr

newarray=array(csvstr)

That's off the top of my head - too busy now to develop and test - but get the idea?
0
 
LVL 58

Accepted Solution

by:
harfang earned 1400 total points
ID: 19550950
This is basically a sorting problem. There are many more efficient algorithms than the following, but if you are dealing with about 100 items, it won't make a large difference in the end.

You have CaseNumb(RowCount) filled. You could for instance use:

    Dim i as Integer, j as Integer, n as Integer
    Dim UniqueNumb() as String

    For i = 1 to RowCount
        ' do we have that value?
        For j = 1 to n
            If CaseNumb(i) = UniqueNumb(j) Then Exit For
        Next j
        If j > n Then
            ' we don't, let's add it:
            n = n + 1
            ReDim Preserve UniqueNumb(1 to n)
            UniqueNumb(n) = CaseNumb(i)
        End If
    Next i

The above is untested, but you get the idea.
(°v°)
0
 

Author Comment

by:JA67
ID: 19551018
Thanks to both of you. I will try your suggestions and see which one I can get to work. I will work with your's first GRayL, simply because you posted first.
0
 

Author Comment

by:JA67
ID: 19551239
OK -- I am having an easier time with your suggestion harfang. I'm sure it's just me...

Anyway, this is very close to working for me. In my test data, I have 12 rows in the listview. Eleven of the numbers in this array are the same, "1995-0001365" and one number is this, "1999-0000804". What I am getting when I look at the results in a MsgBox is two numbers, but they are both the "1999-0000804". I should have it and one instance of "1995-0001365".  In my listview control, the "...804" number is the last row, if that helps any.

Could one or both of you tweak this a little for me? Thank you so much!!

JA67
0
 
LVL 58

Expert Comment

by:harfang
ID: 19551364
I tested my code and in fact it worked without any change (lucky this time!). At the end, you should have 'n' elements in UniqueNumbs, namely:

    UniqueNumbs(1) = "1995-0001365"
    UniqueNumbs(2) = "1999-0000804"

Anyway, this is what I got.
(°v°)
0
 

Author Comment

by:JA67
ID: 19551412
Gosh -- I sure hate to keep bugging you with this.. But let me push my luch a little more...

When I put a MsgBox in the code as you see below. I get 10 blank message boxes, and the last one gives me "1999-0000804".  Am I just trying to view the results in a silly way or do I have a real issue?

Thanks,
JA67
0
 

Author Comment

by:JA67
ID: 19551413
Forgot to include the code....

Dim j As Integer, n As Integer
    Dim UniqueNumb() As String

    For i = 1 To RowCount
    ' do we have that value?
        For j = 1 To n
            If CaseNumb(i) = UniqueNumb(j) Then Exit For
            Next j
        If j > n Then
            ' we don't, let's add it:
            n = n + 1
            ReDim Preserve UniqueNumb(1 To n)
            UniqueNumb(n) = CaseNumb(i)
        End If

    MsgBox UniqueNumb(n)
   
    Next i
0
 
LVL 58

Expert Comment

by:harfang
ID: 19551463
With the msgbox placed there, I see 'RowCount' message, never blank. Can you also show CaseNumb(i)? e.g.:

MsgBox "old array:" & CaseNumb(i) & vbCr & "new array:" & UniqueNumb(n)

(°v°)
0
 

Author Comment

by:JA67
ID: 19551490
When I use the MsgBox you just gave me, I get ten boxes like this:

old array:
new arry:

and the last one is like this:

old array:1999-0000804
new arry:1999-0000804

I wonder what is going on?
0
 
LVL 58

Expert Comment

by:harfang
ID: 19551529
This means that CaseNumb(1 through 9) contain a zero-length string or just blanks. You should review the code where you fill that array.

Note: if you step through the code (for example after a break point), watch the "locals window" and observe how your array gets filled. You will find the bug in no time.

(°v°)
0
 

Author Comment

by:JA67
ID: 19551662
Wow! That local window feature is really cool! I have never seen that before! Incredible! Thanks for that tip!

I am begining to see a problem in the area you pointed out. I looks like I've set the array to equal the total number of rows  and then never increment it on top of that... I'm sure there is more. I am very slow. I will go ahead and award the points. I greatly appreciate the help of both of you. You are painfully good at this stuff!

Thank you!
JA67
0
 
LVL 58

Expert Comment

by:harfang
ID: 19551681
Welcome and good luck with your debugging!
(°v°)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question