query to populate field problem

[installer69]

Sorry to be a pain, here is a sample from my table

ID Serial actionDate action location status
562 0001 22/09/2004 add stock warehouse good
563 0002 22/09/2004 add stock warehouse good
564 0003 22/09/2004 add stock warehouse good
565 0004 22/09/2004 add stock warehouse good
762 0001 23/09/2004 issue andy good
763 0002 23/09/2004 issue bob good
764 0004 23/09/2004 issue brian good
765 0003 23/09/2004 issue craig good
766 0001 23/09/2004 return warehouse good
767 0002 23/09/2004 return warehouse good
768 0003 23/09/2004 return warehouse faulty

 

[lespaul]

and what do you want to do with this table?

Leslie

 

[installer69]

Damn, hit submit instead of preview.

I have a data entry form that updates the above table if an item with a particular serial number is, say, returned. The serial number entry combobox data is limited by query to only items whose last location is 'warehouse'. (by maxActiondate) Another combo box looks up the last 'status' but because the query repopulates after entering the 'serial' to allow for the fact that the last serial is now no longer available to send back it sets the 'status' to the new value which is blank. Is there a way to write a query that will return the value before max of the 'actiondate field'?

Sorry if I've not made my question very clear but it's harder to explain it than write it!!