?
Solved

DLookup in Access

Posted on 2014-11-13
9
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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 51

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 51

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

771 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