I'm trying to find the closest match but it gives me all the ones that match. I'm using access 2010.
SELECT [dbo_test log].[Cabinet S/N], Right([dbo_test log]![Cabinet S/N],3) AS Num, IIf(Right([dbo_test log]![Cabinet S/N],3)<[Ending_Machine_Num]![Ending_Num],[Ending_Machine_Num]![Ending_Num]) AS Closest_Match, Ending_Machine_Num.shipping_date
FROM [dbo_test log] LEFT JOIN Ending_Machine_Num ON [dbo_test log].[Machine Type] = Ending_Machine_Num.[Machine Type]
WHERE ((([dbo_test log].Finish)=False)) and abs([ending_num]- Right([dbo_test log]![Cabinet S/N],3));