Nearest Date in Query, Problem

G Marshall

Board Regular
Joined
Dec 31, 2002
Messages
134
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.)
 
Upvote 0
(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]))
 
Upvote 0

Forum statistics

Threads
1,216,517
Messages
6,131,118
Members
449,622
Latest member
lejohnson93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top