473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
6 1530
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
Martin, why not just filter the recordset?

See:
http://www.mvps.org/access/modules/mdl0010.htm
Nov 13 '05 #3
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Oeln | last post by:
If I want to check for input of an integer I've got the following (I get the form input with $input = "$_POST"): if(!ereg("^+$",$_POST)) { echo "Input is incomplete or incorrect."; } If,...
8
by: hello smith | last post by:
Hello, I have an unsigned char array. I want to determine if each char's ascii value is less than 127. Is there a faster way than looping through the characters and checking if each is less than...
6
by: Jozef Jarosciak | last post by:
Quickest way to find the string in 1 dimensional string array! I have a queue 1 dimensional array of strings called 'queue' and I need a fast way to search it. Once there is match, I don't need...
4
by: Darrel | last post by:
I'm trying to add an extra layer of error checking on a Drop Down List. The list is populated from one table, and then I select the selectedValue from another DB. While it SHOULDN'T ever happen,...
2
by: Emmanuel | last post by:
Hi there, My client would like to process an xml file. the structure of which is as below. <xml> <stockitem> <releaseddate>.....date value...</releaseddate> <...aditional tags for additional...
21
by: jacob navia | last post by:
Many compilers check printf for errors, lcc-win32 too. But there are other functions that would be worth to check, specially memset. Memset is used mainly to clear a memory zone, receiving a...
4
by: Patient Guy | last post by:
Does anyone have any coding rules they follow when doing argument checking? When arguments fail during check, do you return from the call with an ambiguous return value, or do you throw...
4
by: Bob | last post by:
Hi all, I'm trying to import data, modify the data then insert it into a new table. The code below works fine for it but it takes a really long time for 15,000 odd records. Is there a way I...
4
by: reinhout | last post by:
Hi everyone, I have made a script that checks if content is entered in the html boxes and if the required fields are empty, their border lights red. This all works, but if I try to make a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.