I have 2 tables:
"LookupDates" which has
ContractYear
YearStart
YearEnd
and "Staff" which has
Name
StartDate
The tables are not linked in anyway as the LookupDates table is used for referencing.
What I want to be able to do is add another column to the staff table that shows what contract year they started in.
I have tried DLookUp but I have read that you cant use update queries with DLookup in the way that I want to...
DLookup("[ContractYear]", "LookupDates", "[StartDate] Between [YearStart] And [YearEnd]
If I create a form and cycle through the records I can get the DLookup function to work via VBA but this isn't very practical
Can anyone help with this problem?
P.S. I didn't put this post in the queries forum as I think it falls under the General section.
Thanks for any help
"LookupDates" which has
ContractYear
YearStart
YearEnd
and "Staff" which has
Name
StartDate
The tables are not linked in anyway as the LookupDates table is used for referencing.
What I want to be able to do is add another column to the staff table that shows what contract year they started in.
I have tried DLookUp but I have read that you cant use update queries with DLookup in the way that I want to...
DLookup("[ContractYear]", "LookupDates", "[StartDate] Between [YearStart] And [YearEnd]
If I create a form and cycle through the records I can get the DLookup function to work via VBA but this isn't very practical
Can anyone help with this problem?
P.S. I didn't put this post in the queries forum as I think it falls under the General section.
Thanks for any help