closest match too a field...

[marcus.]

Hi

I'm trying to find find the information on the movement of stock at the cloest match to a certain time ..

I have Stock Code and Date/Time
I want to look up the nearest transaction for that stock code around that Date/Time.

ie., if the Date/Time was 12:00 01 January 2004, i want to find the cloest record for movement of that stock.. ie., whether it be one day or two days after...

How can I connect these fields? In excel and the data was inorder of Time, I could use something like

=vlookup('stockcode&date_time','data:stockcode&date_time,transactionRef',2,true

Where true returns the closest match when the records are inorder.... (if you can understand my psudo excel something language up there!). So i'll try and detail my process

for ex..
'stockcode&date_time' = 90005_20040302_120

''data:stockcode&date_time' = a table as displayed below
stockcode&date_time transactionRe
90004_20040302_1023 Ref20
90004_20040302_1544 Ref20
90005_20040302_1023 Ref20
90005_20040302_1045 Ref20
90005_20040302_1333 Ref20

excel would return Ref206

Obviously I've greatly simplified thing (firstly only a few records), have joined fields together (stock code and time stamp) along with converting the time stamp into something which can be decified in numerical order, and sorted the records based on this field. But hopefully you understand what I am trying to achieve

Any ideas
Thanks
Marcu

 

[marcus.]

woops, was meant to be posted in 'Access Queries'..

but if you can help, i'll check either group

thanks
Marcus