Solved

DLookup in Access

Posted on 2014-11-13
9
243 Views
Last Modified: 2014-11-19
The goal is the retrieve an ID from table B into table A, based on a date criteria and a range criteria.

Table A:
CountDay, DateA
1, 2013-09-13
2, 2013-09-13
3, 2013-09-13
4, 2013-09-13
5, 2013-09-13
1, 2013-09-14
2, 2013-09-14
3, 2013-09-14

Table B:
ID, DateB, StartCount, EndCount
1,2013-09-13,1,3
2,2013-09-13,4,5
3,2013-09-14,1,2
4,2013-09-13,3,5

I'd like the output to have a new column with the ID from table B:

CountDay, DateA, ID
1, 2013-09-13, 1
2, 2013-09-13, 1
3, 2013-09-13, 1
4, 2013-09-13, 2
5, 2013-09-13, 2
1, 2013-09-14, 3
2, 2013-09-14, 3
3, 2013-09-14, 4

Currently I'm using a SQL code combined with a VBA function to do this, but it's not quite working:
 UPDATE TableA SET ID = FindID([CountDay],[DateA]);

Open in new window

Public Function FindID(Count As Double, DateMatch As Date)
Application.DBEngine.SetOption dbMaxLocksPerFile, 50000000
FindID = DLookup("ID", "TableB", DateMatch = [DateB] & " And CStr(Count) Between [StartCount] AND [EndCount]")
End Function

Open in new window


The second part of the VBA code seems to be working, but the first part (with the date) keeps on saying "Compile Error: External name not defined". Can anyone please help with this?

Also not sure if this is the right/most efficient way of doing this, so any alternative would be appreciated!

Thank you!
0
Comment
Question by:cathyha
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Expert Comment

by:TONY TAYLOR
ID: 40441098
FindID = DLookup("ID", "TableB", "'" & DateMatch & "' = DateB And " & CStr(Count) & " Between [StartCount] AND [EndCount]")

Open in new window


The third parameter is a text string that when parsed by the VBA would be the where clause in a query.

The way you had it "DateB" was attempting to parsed as a value in VBA and it could not understand it.
0
 

Author Comment

by:cathyha
ID: 40441145
Still didn't work - Access says that there's a type mismatch
0
 
LVL 5

Expert Comment

by:TONY TAYLOR
ID: 40441167
Then my guess is that DateB is not actually a date field... Try this
FindID = DLookup("ID", "TableB", "'" & Format(DateMatch, "yyyy-mm-dd") & "' = DateB And " & CStr(Count) & " Between [StartCount] AND [EndCount]")

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:cathyha
ID: 40441247
Nope...still not working.

I'm using this code now, which is running without error but isn't returning any matches

Public Function FindID(Count As Double, DateMatch As String)
Application.DBEngine.SetOption dbMaxLocksPerFile, 50000000
FindID = DLookup("ID", "TableB", " " & DateMatch & " = [DateB] And [StartCount] <= " & Count & " And " & Count & " <= [EndCount]")
End Function

Open in new window

0
 
LVL 5

Accepted Solution

by:
TONY TAYLOR earned 500 total points
ID: 40441265
Crud!! I was thinking SQL Server!!  It's pounds NOT quotes!!

FindID = DLookup("ID", "TableB", "#" & DateMatch & "# = DateB And " & CStr(Count) & " Between [StartCount] AND [EndCount]")

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40442004
This is how you normally would do this.

Move
    Application.DBEngine.SetOption dbMaxLocksPerFile, 50000000
outside this function. It should be called somewhere else once.

Then, as CStr will fail in a non-US environment:

FindID = DLookup("ID", "TableB", "[DateB] = #" & Format(DateMatch, "yyyy\/mm\/dd") & "# And " & Str(Count) & " Between [StartCount] And [EndCount]")

/gustav
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40444214
I generally use this little function to avoid date formating issues in SQL strings

Public Function SqlDate(myDate As Date) As String
   SqlDate = "#" & Month(myDate) & "/" & Day(myDate) & "/" & Year(myDate) & "#"
End Function

so your filter would read

"[DateB] =" & sqlDate(DateMatch) & " And " & Str(Count) & " Between [StartCount] And [EndCount]"
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40444328
But you have to escape the slash or it will be read as the localized date separator which may be a space, though typically a / or - .

/gustav
0
 

Author Closing Comment

by:cathyha
ID: 40453207
Thank you! That helped to solve the problem!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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