×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FAST FIND in big array

FAST FIND in big array

FAST FIND in big array

(OP)
my code:

CODE

...
IN MODULE
PUBLIC BIG_ARRAY() As Variant
IN MODULE
 
'BIG ARRAY
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI UNION SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM STATI "
    Set RSB = New ADODB.Recordset
    RSB.CursorLocation = adUseClient
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI"
    RSB.Open Source:=SQL, _
             ActiveConnection:=CON, _
             CursorType:=adOpenForwardOnly, _
             LockType:=adLockReadOnly
    RSB.Sort = ("RIGA")

    RSB.MoveFirst
    Erase BIG_ARRAY()
    BIG_ARRAY = RSC.GetRows()
    RSB.Close
    Set RSB = Nothing
    'BIG ARRAY 

Possible to find MyString ( as string dimensioned) without loop the array (it contain approx 150000 item!)

and return a booelean=true is the string is in array.

my test esperiment but dont work!!!

STRINGA = UBound(Filter(BIG_ARRAY, Me.CCOMNASC.Text)) > -1

STRINGA = InStr(1, Join(BIG_ARRAY, vbNullChar), Me.CCOMNASC.Text) > 0

If InStr(Join(BIG_ARRAY), Me.CCOMNASC.Text) > 0 Then Stop

RE: FAST FIND in big array

So, you have MyString variable that contains whatever you are looking for, and a couple of tables in your data base: COMUNI and STATI
When you create a recordset, that’s an array. Then you copy this array into another array called BIG_ARRAY And now you are trying to find out if MyString is anywhere in your BIG_ARRAY?

Why not simply create a small recordset where you can ask for whatever you are looking for directly from your data base? Something like:

SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA
FROM COMUNI
WHERE DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' like ('* & MyString & *')

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: FAST FIND in big array

Your source is a database - which is really rather good at finding stuff ...

So why not leverage that?

E.g. (and a reminder this is an illustration of how to do this, NOT production code)

CODE

...
IN MODULE
PUBLIC BIG_ARRAY() As Variant
IN MODULE
 
'BIG ARRAY
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI UNION SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM STATI "
    Set RSB = New ADODB.Recordset
    RSB.CursorLocation = adUseClient
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI"
    RSB.Open Source:=SQL, _
             ActiveConnection:=CON, _
             CursorType:=adOpenForwardOnly, _
             LockType:=adLockReadOnly
    RSB.Sort = ("RIGA")

    RSB.MoveFirst
    RSB.Filter="RIGA LIKE '*" & Me.CCOMNASC.Text & "*'"
    If RSB.RecordCount > 0 Then msgbox "String currently in Me.CCOMNASC.Text found in recordset RSB"
    RSB.Close
    Set RSB = Nothing
    'BIG ARRAY  

RE: FAST FIND in big array

No.
I need bigarray!
I use this for other parte of code.
My really problem Is to find if string exists in Array.
The Array contain approx 150.000 item!
I know the day ti find string by for next looping.
But i need a fast method to find string in Array without looping.

RE: FAST FIND in big array

Quote (sal21)

Possible to find MyString ( as string dimensioned) without loop the array (it contain approx 150000 item!) and return a booelean=true is the string is in array.

Both strongm and my logic allows you to set your "booelean=true" when your string is found.
And is it "a fast method to find string in Array without looping" since your "BIG_ARRAY = RSC.GetRows()"

"I need bigarray!" - well, you can keep your bigarray. :)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: FAST FIND in big array

>I need bigarray!

I suspect that you didn't, to be honest, to start with. Most of the times you have populated bigarray from a recordset (from the various examples of the various problems you have been having that you have posted here), and the reality is that you'd have been better off directly using the recordset rather than toying with bigarray. But you made a design decision, and I guess it is now too late.



RE: FAST FIND in big array

I have seen similar approach before: creating a recordset, then copy the data into an array, then copy some or all elements into another array, and then again into something else. Then modifying some of the data in some of the array elements. At the end, nobody knows what is what and where is the data that you want to use. Especially when the names of the variables are very similar.
It is a pain when the code needs to be modified, even to a person who wrote it. But it is a nightmare to modify it by somebody else. In most cases like this, I simply rewrite the code because it is easier and faster that trying to untangle the existing mess.
I am not saying that is in this case, just a word of warning... hairpull

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: FAST FIND in big array

That being said, not quite sure why Filter didn't work for you in this particular case.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close