DLookup in Access

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!
cathyhaAsked:
Who is Participating?
 
TONY TAYLORConnect With a Mentor Commented:
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
 
TONY TAYLORCommented:
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
 
cathyhaAuthor Commented:
Still didn't work - Access says that there's a type mismatch
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TONY TAYLORCommented:
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
 
cathyhaAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
bonjour-autCommented:
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
 
Gustav BrockCIOCommented:
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
 
cathyhaAuthor Commented:
Thank you! That helped to solve the problem!
0
All Courses

From novice to tech pro — start learning today.