Best way to update data

[d00ku]

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

 

[Smart]

The code is untested and off the top of my head so the syntax may not be right but at least it might give yo some idea

create a select query

select lookupdates.contractyear,lookupdates.yearstart,lookupdates.yearend,
staff.startdate
from lookupdates,staff
where staff.startdate between lookupdates.yearstart and lookupdates.yearend

In your vb code

Dim Found as date

found = nz(dlookup("[contractyear]","[Query name]"),0)

If found = 0 then 'means that a date has not been found '
do something
else
pass the value to wherever

 

[Pat Hartman]

You should not be storing information that is available elsewhere. The contract year can be found any time you need it either with a DLookup() or a non-equi-join.