UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> VBA - Search Value In Table, Access 2016    
 
   
behedwin
post Dec 18 2018, 05:13 AM
Post#1



Posts: 38
Joined: 29-October 17



Hi

I want to make an if statement that looks for a matching value in a table´s column.

Table name: table1
column: name

I have a value being held in a variable called "username" (dont know if im explaining this correctly)
If i do a code
CODE
          msgbox username


i do get the expected value i want... now i want to look if that value is stored in a table in a column.

CODE
If table1,column=name = username then
msgbox "value found in table"
else
msgbox "value not found in table"
end if


from what i have read i should do a DLookup line.... but i dont know how to write that.
Anyone able to help me?
Go to the top of the page
 
cheekybuddha
post Dec 18 2018, 06:07 AM
Post#2


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


Actually, a DCount() would serve you better in this instance.

DLookup() will return the found value to you (or Null if it doesn't exist in the table), but you already have it and therefore it's not necessary. You also have to do some extra processing in the case Null is returned.

DCount() will tell you how many occurrences of the value you search for are in the field in the table. If it's > 0 then it exists.

The first argument of the DCount() function is the field to count, the second is the table name, the third is the criteria. You can use:
CODE
  If DCount("*", "table1", "[name] = '" & username & "'") Then
    MsgBox "value found in table"
  Else ...


As a side note, avoid using 'name' as a field name - it is a reserved word. In the example above I surrounded it with square brackets to prevent it confusing Access, but it's a pain to have to remember to do that every time.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 18 2018, 09:49 AM
Post#3


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


Pardon me for jumping in, but I tend to use Dlookup to find if something is there at all or not. The reason is, suppose you have a table with 1,000,000 records and the value you're looking for is in 900,000 of them. But you only need to know if it's there once. I have never actually tested Dlookup versus Dcount, but it seems to me that it could take longer for the computer to count them than just to find one. And if I don't need the count I opt for Dlookup.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 18 2018, 09:55 AM
Post#4


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


Hi Robert,

Dlookup() will still find 900,000 even if it only returns it once, no?

Also, you have to muck around with:
CODE
  If Not IsNull(DLookup("[name]", "table1", "[name] = '" & username & "'")) Then ...

which is more confusing to decipher than a straight DCount()

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 18 2018, 10:08 AM
Post#5


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


I don't know. As I said I never tested it. But Dcount would necessarily have to look at all the records.

People with small tables aren't likely to notice a wait with Dcount. And maybe they never will. But what if the business grows? Good practice. Not do-it-or-the-sky-will-fall-in. But good practice, I think.

As far as a little more code, are you kidding?

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 18 2018, 10:26 AM
Post#6


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> But Dcount would necessarily have to look at all the records. <<

Not strictly speaking true - when the field is defined as "*" then, if the field to be matched in the criteria is indexed, it *should* use the index to count the matches.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Dec 18 2018, 10:29 AM
Post#7


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> As far as a little more code, are you kidding? <<

Yeah, I'm lazy!!

No, but in more seriousness, for someone reading the code who only has basic VBA skills, the whole testing for Null around the main expression can be confusing.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Dec 18 2018, 10:29 AM
Post#8


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


I suppose testing is the only real solution to the quandary

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 18 2018, 11:00 AM
Post#9


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


Think how limited you would be if you avoided code that was potentially confusing to persons with basic VBA skills.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Dec 18 2018, 11:18 AM
Post#10


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


I'm using common-sense logic on this matter. I'm too lazy to, too! (To test it, that is.) I'd welcome some evidence one way or the other.

In lieu of that, what would you say is the largest table (ballpark figure) you've used Dcount on?

--------------------
Robert Crouser
Go to the top of the page
 
PaulBrand
post Dec 18 2018, 11:53 AM
Post#11



Posts: 1,705
Joined: 4-September 02
From: Oxford UK


Personally, I find domain functions carry too much overhead - essentially looping a recordset. I always try the outline below. I've got a function for this which is broader - but for the OP this is a good place to start.

CODE
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT [name] FROM table1 WHERE   [name] = '" & UserName & "'"

Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
    MsgBox "Yes"
End If

--------------------
Paul
Go to the top of the page
 
projecttoday
post Dec 18 2018, 12:06 PM
Post#12


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


Interesting. Have you done any comparison testing?


--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 19 2018, 09:39 AM
Post#13


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


Robert, I see you edited your slightly narky repsonse! wink.gif

Of course, I didn't mean you should simplify your own code! But, remember the OP in this thread is new to DLookup()/DCount(), so keeping it simple might be helpful!

Anyway, I thought I'd do some testing.

I created a table with a PK and two test fields, one indexed, the other not.

I then loaded it with 2 million records - random strings between 5 and 15 characters from a lowercase alphabet including the space character. The same value in both the indexed and non-indexed fields.

Within the 2 million records I inserted at random positions 12344 records with the value 'find me'.

Then I ran 8 tests:
1. DLookup() to find if value exists in table, indexed field
2. DLookup() to find if value exists in table, un-indexed field
3. DCount() to find if value exists in table, indexed field
4. DCount() to find if value exists in table, un-indexed field
5. SQL COUNT(*) to find if value exists in table, indexed field
6. SQL COUNT(*) to find if value exists in table, un-indexed field
7. SQL SELECT field to find if value exists in table, indexed field
8. SQL SELECT field to find if value exists in table, un-indexed field

Each test was run 1000 times in a loop and the total time elapsed output for comparison.

The results bear out your hypothesis, Robert:
CODE
?TimingTests(1000)
Test started: 2018-12-19 13:37
tblSearch     2000000 records
find me       12344 occurrences

1. DLookup() to find if value exists in table, indexed field:
VBA: blRet = Not IsNull(DLookup("fldIndexed", "tblSearch", "fldIndexed = 'find me'"))
1000 iterations: 00:00:00.857

2. DLookup() to find if value exists in table, un-indexed field:
VBA: blRet = Not IsNull(DLookup("fldNonIndexed", "tblSearch", "fldNonIndexed = 'find me'"))
1000 iterations: 00:00:00.663

3. DCount() to find if value exists in table, indexed field:
VBA: blRet = DCount("*", "tblSearch", "fldIndexed = 'find me'") > 0
1000 iterations: 00:00:01.18

4. DCount() to find if value exists in table, un-indexed field:
VBA: blRet = DCount("*", "tblSearch", "fldNonIndexed = 'find me'") > 0
1000 iterations: 00:09:53.424

5. SQL COUNT(*) to find if value exists in table, indexed field:
VBA: With CurrentDb.OpenRecordset(SELECT COUNT(*) FROM tblSearch WHERE fldIndexed = 'find me';)
       blRet = .Fields(0) > 0
      .Close
     End With
1000 iterations: 00:00:01.234

6. SQL COUNT(*) to find if value exists in table, un-indexed field:
VBA: With CurrentDb.OpenRecordset(SELECT COUNT(*) FROM tblSearch WHERE fldNonIndexed = 'find me';)
       blRet = .Fields(0) > 0
      .Close
     End With
1000 iterations: 00:09:48.731

7. SQL SELECT field to find if value exists in table, indexed field:
VBA: With CurrentDb.OpenRecordset(SELECT fldIndexed FROM tblSearch WHERE fldIndexed = 'find me';)
       blRet = .RecordCount > 0
      .Close
     End With
1000 iterations: 00:00:00.878

8. SQL SELECT field to find if value exists in table, un-indexed field:
VBA: With CurrentDb.OpenRecordset(SELECT fldNonIndexed FROM tblSearch WHERE fldNonIndexed = 'find me';)
       blRet = .RecordCount > 0
      .Close
     End With
1000 iterations: 00:00:00.879

True


The real takeaway is not to use DCount() where the criteria field is unindexed! (One shouldn't search on an unindexed field really anyway!)

The fastest result, however, seems to be using DLookup() where the criteria field is unindexed!!! dontknow.gif

I attach the test db. Please examine it to make sure I haven't made any glaring errors in my methodology.

(If you fancy emptying the table and using a fresh set of data for tests, beware - it took about 2 3/4 hours last night to load 2 million records!)

OK, after zipping, the db with 2m records is about 45 MB !!

So I'm attaching a virgin version.

If you want to test, you will have to load the data. In the Immediate Window (Ctrl+G) run:
CODE
?LoadData

and find something to do for a couple of hours! You can adjust the constants in the procedure first to specify the amount of records you wish to create, and how many 'find me's you wish to insert.

To run the tests, also in the Immediate Window, run:
CODE
?TimingTests(1000)

You can choose how many iterations you wish to pass, but note that the DCount() on the unindexed field took about 10 minutes!

Enjoy!

d
Attached File(s)
Attached File  20181219_DLookupTest___Empty.zip ( 36.43K )Number of downloads: 23
 

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 19 2018, 10:03 AM
Post#14


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


Wow, I'm impressed with your resourcefulness! This information could be helpful to many. Thanks for confirming my suspicions. We make a good pair.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 19 2018, 10:55 AM
Post#15


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> We make a good pair. <<
Definitely!

Hopefully, we all learn something!

@behedwin, apologies if we've hijacked your thread somewhat, but I hope we've answered your question. If not, let us know.

Unless you are dealing with millions of records, either DCount() or DLookup() will provide the answer you seek wiyhout any noticeable performance difference.

IMHO DCount() is simpler to implement but, as Robert points out, if your record base grows significantly then DLookup() will be a better bet as the DCount() performance degrades, so perhaps better to implement from the off.

Good advice in any case is to ensure you have an index on the field you search on.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Dec 24 2018, 01:53 PM
Post#16


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Just found this after clicking on a link.
If anyone is interested, I have done a whole series of speed comparison tests with results and sample databases
See http://www.mendipdatasystems.co.UK/speed-c...ests/4594424200
This post has been edited by isladogs: Dec 24 2018, 01:53 PM

--------------------
Go to the top of the page
 
DigooCuba
post Dec 27 2018, 06:50 PM
Post#17



Posts: 17
Joined: 24-December 18



Just jumping in this thread!

After stating:

QUOTE
>> As far as a little more code, are you kidding? <<

Yeah, I'm lazy!!


cheekybuddha goes and prepare a test to assess perfomance of 8 different ways to compare data and present important results. I can only imagine what you can do if you wake one day and decides to work hard... sarcasm.gif

IMHO about cheekybuddha:

uarulez2.gif

Cheers,
Diogo Cuba
This post has been edited by DigooCuba: Dec 27 2018, 06:51 PM
Go to the top of the page
 
isladogs
post Jan 28 2019, 07:37 AM
Post#18


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Hi
Its taken me a while to get around to it, but inspired by David's example in post #13, I've taken the same idea a bit further with another in my ever growing series of speed comparison tests on my website. See item 7 - Check Record Exists

As in David's example, the tests compare the time required to check the existence of a specified record in a large data table using 4 different methods: DLookup / DCount / SQLCount(*) / SQL Select

I've done two similar versions of this set of speed tests. The main difference is the search field is NOT INDEXED in one version and INDEXED in the other.

Of course, in a real world application, fields being searched regularly should normally be INDEXED.
The index increases file size but dramatically reduce search times.
In this example, the search time was over 250x faster using indexing but there are some other interesting differences in the results for each version.

In each case, a 'reference' table tblSource containing 10,000 different UK postcodes (deliberately kept small to reduce file size) is used to populate the test table tblData which is initially empty.

In order to get a large data table, those records are appended repeatedly
For example, 100 batches (default) of 10,000 records to give a total 1 million records in tblData.

One RANDOM record is then replaced by a 'dummy' postcode 'XM4 5HQ' used in the record check.
For info, this postcode is used to sort letters addressed to Santa Claus!!!

Each speed test is was done repeatedly by looping through the code multiple times and measuring the total time
The number of loops used can be varied with default=100.
So, in this case 1 million records are looped through 100 times i.e. 100 million records checked.

In the non-indexed tests, DLookup & SQL SELECT were similar to each other and faster than DCount or SQL Count(*) - often MUCH faster
The difference depended on the position of the record being searched (as you would expect)

For the indexed field, all 4 results were similar - SQL SELECT was always slowest. DLookup & DCount were, perhaps surprisingly, almost fastest (though not by much)
The real power of indexing was confirmed by increasing the test table to 10 million records.
The times required were identical to those for 1 million records.

In fact, I also tested with 30 million records - once again indexing meant the total times were the same.
However it took several hours to create that ridiculously large test table and the database grew to 1.9 GB - so NOT recommended!

See attached for the 2 example databases and a PDF version of the website article

I would appreciate feedback on any of the website articles - some produce some surprising results e.g. HAVING vs WHERE
If you wish to respond, the easiest way is probably to email me using the link in my signature line
This post has been edited by isladogs: Jan 28 2019, 08:31 AM
Attached File(s)
Attached File  SpeedComparisonTests_v9.0b___CheckRecordExists___NOT_INDEXED.zip ( 398.26K )Number of downloads: 6
Attached File  SpeedComparisonTests_v9.0a___CheckRecordExists___INDEXED.zip ( 399.14K )Number of downloads: 3
Attached File  Speed_Tests___CheckRecordExists.pdf ( 697.92K )Number of downloads: 4
 

--------------------
Go to the top of the page
 
cheekybuddha
post Jan 28 2019, 07:45 AM
Post#19


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> The main difference is the search field is NOT INDEXED in one and INDEXED in the other. <<

Hmmm... I think my tests specifically tested both indexed and non-indexed search fields wink.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Jan 28 2019, 08:19 AM
Post#20


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Your tests did indeed.
I meant I chose to do both tests on 2 separate files. Sorry if that wasn't clear

I also did some additional tests for both non-indexed & indexed
- modifying position of record being searched (chosen randomly) and varying number of records

This affects the results for non-indexed fields and of course indexing changes how the search is performed

Please have a look at the PDF or the website article to see how I've built on your excellent foundation
This post has been edited by isladogs: Jan 28 2019, 08:21 AM

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 03:54 PM