Update A Table With Details From Another

Hi,

I have not used Access for over 10 years! I am using Access 2000 to look at tracking cow weight over time!

There are 2 tables:
- main table
- weight table

I want to set the main table's January weight to a value from the Animal weight table (logical expression):

UPDATE [main table]
SET [main table].Jan07 = [Animal weight].[Kg]
WHERE [main table].[Line no])=[Animal Weight].[Line no] AND
[Animal weight].[weigh date]= 10/01/2007;

I have tried several times to get this working, but it tends to prompt for the weight to be given as an input.

Any ideas?

many thanks

Lactaman