Update query for closest date

[schmidty3]

I have two tables - both have dates in them, and Table A has an additional date field that is currently blank. I want to populate that with the date from Table B that is equal to or the next biggest as the date in the other date field in Table A. I wrote this, but it's not working:

UPDATE TableA, TableB SET TableA.New_Date = TableB.B_Date
WHERE (((TableA.Old_Date)<=TableB.B_Date));

Any ideas?

 

[PHV]

You want all occurrences of TableA having the same date ?
UPDATE TableA
SET New_Date = (SELECT Min(B_Date) FROM TableB WHERE B_Date>=TableA.Old_Date);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[schmidty3]

Access didn't like that format. It said that there needs to be something updatable in the query.
Any other suggestions?

 

[PHV]

And this ?
UPDATE TableA
SET New_Date = DMin("B_Date", "TableB", "B_Date>=#" & [Old_Date] & "#");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[schmidty3]

Perfect. Thank you so much!