Help with Dlookup

[buttons]

Hi can somebody help me with a Dlookup issue. I am testing some code which runs from the "After Update Event" to enter a working day into the End Date Field. I have created a table with a list of working days and a reference against each one (Working Days table). I want the dLookup to return the reference against the working day based on the date entered into Start Date in Table1.
In this test I have two tables : Table1, containing Start Date (date/time), End Date (date/time), Start Date Ref (text), End Date ref (text).
Working Days table, containing Ref (Text) and Working_day (text)
The code used is as follows:

Private Sub Start_Date_AfterUpdate()

Dim lookup_ref As Integer
Dim return_ref As String
Dim start As String

start = [Start Date]

lookup_ref = DLookup("[Ref]", "working days table", "[working_day] = start")

[start date ref] = lookup_ref
[end date ref] = lookup_ref + 5

return_ref = DLookup("[working_day]", "working days table", "[ref] = [table1]![end date ref]")

[end date] = return_ref

End Sub

When running the code I get “Run Time error 2001”
I am sure this is something very simple but it’s being to drive me mad!!!!
Cheers

 

[maxmangion]

probably the line causing the problem is this:

lookup_ref = DLookup("[Ref]", "working days table", "[working_day] = start")

try

lookup_ref = DLookup("[Ref]", "working days table", "[working_day] = '" & start & "'")

 

[buttons]

Many thanks, seems to have solved the problem.

 

[maxmangion]

you are welcome

And welcome to the forums!