By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,691 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,691 IT Pros & Developers. It's quick & easy.

a2k - quickest way of checking for a value in an array?

P: n/a
I think my brain has short-circuited again :)

Is this the quickest way to check for the existence of a given value in an
array?

e.g

For i = 0 To rrst.RecordCount
If myArray(i) = DLookup("ID", "tblmain", "Salary = " &
varSomeValue) Then
Exit sub
End If
Next

I would like this lookup to be a bit quicker. This code means looping
through every item in the array - is there a shortcut or is this OK?

thanks
Martin
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Mon, 5 Jul 2004 12:37:31 +0100, "Deano" <de*********@hotmail.com>
wrote:

No it doesn't. It means (on average) looping tthough 50% of the items
in the array.
Otherwise, I don't understand your code at all. It appears as if
myArray is filled with data from the db. If so, you can execute this
entire lookup in SQL - much faster.
It appears the DLookup term is constant. You can call it outside of
the loop.

-Tom.

I think my brain has short-circuited again :)

Is this the quickest way to check for the existence of a given value in an
array?

e.g

For i = 0 To rrst.RecordCount
If myArray(i) = DLookup("ID", "tblmain", "Salary = " &
varSomeValue) Then
Exit sub
End If
Next

I would like this lookup to be a bit quicker. This code means looping
through every item in the array - is there a shortcut or is this OK?

thanks
Martin


Nov 13 '05 #2

P: n/a
Martin, why not just filter the recordset?

See:
http://www.mvps.org/access/modules/mdl0010.htm
Nov 13 '05 #3

P: n/a
so myArray() contains a list of ID's ?
is the array built for IDs in another table / query ?
if so execute,
strsql = "select ID from tblMain where salary = '"
strsql = strsql & varSomeValue & "' and "
strsql = strsql & "ID IN (SELECT ID from othertable where ....)"
set rrst = currentdb.openRecordset(strsql)
if the array isn't built from another table, and it doesn't have too many items
convert the array contents into a comma-delimited string
ie. array(0) = 1
array(1) = 20

strArray = "'1','20'"
strsql = "select ID from tblMain where salary = '"
strsql = strsql & varSomeValue & "' and "
strsql = strsql & "ID IN (" & strArray & ")"
set rrst = currentdb.openRecordset(strsql)

"Deano" <de*********@hotmail.com> wrote in message news:<y2*******************@stones.force9.net>...
I think my brain has short-circuited again :)

Is this the quickest way to check for the existence of a given value in an
array?

e.g

For i = 0 To rrst.RecordCount
If myArray(i) = DLookup("ID", "tblmain", "Salary = " &
varSomeValue) Then
Exit sub
End If
Next

I would like this lookup to be a bit quicker. This code means looping
through every item in the array - is there a shortcut or is this OK?

thanks
Martin

Nov 13 '05 #4

P: n/a
Pieter Linden wrote:
Martin, why not just filter the recordset?

See:
http://www.mvps.org/access/modules/mdl0010.htm


I'll have a look at that.

The problem is my code is part of a convoluted solution to an interesting
problem. I might post *that* problem instead...
Nov 13 '05 #5

P: n/a
In article <y2*******************@stones.force9.net>,
"Deano" <de*********@hotmail.com> wrote:
For i = 0 To rrst.RecordCount
If myArray(i) = DLookup("ID", "tblmain", "Salary = " &
varSomeValue) Then
Exit sub
End If
Next

I would like this lookup to be a bit quicker. This code means looping
through every item in the array - is there a shortcut or is this OK?


On average, you will do the DLookup approximately rrst.RecordCount/2
times, i.e. you will access tblmain again and again, fetching the same
value each time.

Pull the DLookup() out of the loop:

Dim TheSalary as Single (or whatever data type is appropriate)
TheSalary = DLookup("ID", "tblmain", "Salary=" & varSomeValue)
For i=0 to rrst.RecordCount
if myArray(i) = TheSalary then Exit Sub
Next i

-Dave

--
"Sometimes what seems to be enough smoke to guarantee a robust
fire is actually just a cloud of dust from a passing bandwagon."
- Daniel Dennett
Nov 13 '05 #6

P: n/a
And if you need to use code to convert the array to a delimited string, see
the VBA Join function (if using Access 2002 or newer).

On 5 Jul 2004 12:33:39 -0700, le*********@natpro.com (Roger) wrote:
so myArray() contains a list of ID's ?
is the array built for IDs in another table / query ?
if so execute,
strsql = "select ID from tblMain where salary = '"
strsql = strsql & varSomeValue & "' and "
strsql = strsql & "ID IN (SELECT ID from othertable where ....)"
set rrst = currentdb.openRecordset(strsql)
if the array isn't built from another table, and it doesn't have too many items
convert the array contents into a comma-delimited string
ie. array(0) = 1
array(1) = 20

strArray = "'1','20'"
strsql = "select ID from tblMain where salary = '"
strsql = strsql & varSomeValue & "' and "
strsql = strsql & "ID IN (" & strArray & ")"
set rrst = currentdb.openRecordset(strsql)

"Deano" <de*********@hotmail.com> wrote in message news:<y2*******************@stones.force9.net>...
I think my brain has short-circuited again :)

Is this the quickest way to check for the existence of a given value in an
array?

e.g

For i = 0 To rrst.RecordCount
If myArray(i) = DLookup("ID", "tblmain", "Salary = " &
varSomeValue) Then
Exit sub
End If
Next

I would like this lookup to be a bit quicker. This code means looping
through every item in the array - is there a shortcut or is this OK?

thanks
Martin


Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.