Solved

DLookup in Access

Posted on 2014-11-13
9
245 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
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!

 

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 50

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 50

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

685 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