Nearest Date in Query, Problem

[G Marshall]

Hello

It’s a bit difficult to put this one into words, but I will try my best.

I have a table “tblCurrentFuelCosts” with three fields Date, Product and CostPerLitre
Products are Kerosene and GasOil and the fuel costs are updated twice per month.

I also have a table tblConsumptions with fields: Site, ReadingDate, Product and consumption.

I have set up a query referencing these two tables and I want to return a subset that displays, Site, ReadingDate, Product and Consumption from table” tblConsumptions” and CostPerLitre from table “tblCurrentFuelCosts”

My dilemma is this and I just can’t get my head round this problem. In the CostPerLitre field I want to return the CostPerLitre where the ReadingDate from tblConsumptions is the nearest date to the date in the field Date from the tblCurrentFuelCosts.

For example say the ReadingDate in the tblConsumptions is 06/10/2005 and in the tblCurrentFuelCosts there are two dates 02/10/2005 and 12/10/2005 I would want to return the CostPerLitre from the 02/10/2005 which is the date closest to my reading date.

How can I ask this in my query.

I would appreciate any help


Gerald

 

[jonlj]

something like

IIF (ABS([readingdate]-[date1]) "less than" ABS([readingdate]-[date2]),[date1],[date2]))

in a select query

then link this new field in the query to the source of your fuel cost.

(have had to put "less than" instead of < as it wouldnt format correctly with that in the string.)

 

[Corticus]

(have had to put "less than" instead of < as it wouldnt format correctly with that in the string.)

You have to disable html in the post, or your browser thinks your starting an HTML tag.


IIF (ABS([readingdate]-[date1]) < ABS([readingdate]-[date2]),[date1],[date2]))