Date Formula Problem

[G Marshall]

Hi
I am relatively new to Access and I am having problems with a report.
I have a "Fuel Consumption report". Consumption for a particular site is as follows.
Consumption =((Previous Reading ltrs + Deliveries ltrs)-Current Reading ltrs)
where "Deliveries" is the sum of all deliveries between my previous reading date and my current or most recent reading date.
My problem is how to set up my formula to recognise "previous date" and "current date"
I would appreciate any help.

Thanks

Gerald

 

[Russell Hauf]

Have you had success with this yet? If not, how were you thinking of having your query read your 2 dates? Do you want to be prompted for them?

 

[G Marshall]

Hello
Thanks for your interest in my question, I have not had any success as yet.

My Query is linked to two tables, “Tank Readings” and “Deliveries”.

As Delivery invoices are received they are entered in the “Deliveries” table with the fields, “Site” “Delivery Date” and “Quantity”. Each site could have multiple deliveries each month.

Tank readings are taken once per month and entered in the “ Tank Readings” table with the fields “Site” “Reading” and “Date”

Each site name is unique so I was hoping that rather than having to enter dates with a prompt, my query could be set up with a formula that would total the sum of all deliveries using the dates between the most recent, tank reading date and the previous tank reading date of a particular site in the “ Tank Readings” table.

It sounds a bit complicated to me and perhaps it is not possible, but I would appreciate any suggestions.

Thanks for your time.

Cheers

Gerald

 

[Russell Hauf]

Ok - that is a tough one. Here is something you can try (it worked for me). You'll have to change the table and field names. My table names (and fields therein) are:

Tank Readings (Site, Reading, Date)
Deliveries (Site, Quantity, Delivery Date)

My sites were A, B, C, etc. - for this query I was asking for site A only.

Here is my SQL:

• SELECT Sum(Deliveries.Quantity) AS SumOfQuantity
  FROM Deliveries
  WHERE Deliveries.Site="A"
  AND Deliveries.[Delivery Date] Between
  (SELECT Max(X.Date) AS MaxOfDate
  FROM [TANK READINGS] AS X
  WHERE X.Site = [SITE]
  AND X.Date < DMax("[DATE]","[TANK READINGS]","[SITE]='" & [SITE] & "'"))
  AND DMax("[DATE]","[TANK READINGS]","[SITE]='" & [SITE] & "'");

So change my names to yours, and then paste the SQL. If you need help doing this, let us know.

Hope this helps,

Russell

p.s. I recommend to not name date fields "Date", as Date is also a function and the 2 can be confused if you're not careful. I either use something like fldDate or ReadingDate.

 

[G Marshall]

Hi Russell

Thanks for your interest and help, much appreciated. This looks brilliant, I will be trying out your code tomorrow and I will let you know how I get on.

Cheers


Gerald

 

[G Marshall]

Hi Russell

Thanks for your help, I have used your SQL in my query and it works just fine. I am so pleased. There is however one small point you may be able to clarify. In your SQL you are looking for information for site "A" only. How do I change your SQL to include all sites in my table.

Once again thanks for your interest and your solution.


Cheers


Gerald

 

[Russell Hauf]

Hi again, Gerald. Ok, I think that I actually found that my first query didn't always return the correct results (but definitely did under certain conditions). I wasn't able to make this work with one query, but I did with 2. Try this:

-First, make a query like this one (again, changing any table or field names as in my last post):

• SELECT Y.Site, Y.Date, (SELECT COUNT(*) FROM [Tank Readings] WHERE Site = Y.Site and [Date] > Y.[Date]) AS LastDates
  FROM [Tank Readings] AS Y
  WHERE ((((SELECT COUNT(*) FROM [Tank Readings] WHERE Site = Y.Site and [Date] > Y.[Date]))<=1))
  ORDER BY Y.Date DESC;

Then name this something like qryLast2Readings. Ok, then the query to get the readings for all sites would look something like this:

• SELECT Sum(Deliveries.Quantity) AS SumOfQuantity, Deliveries.Site
  FROM Deliveries
  WHERE (((Deliveries.[Delivery Date]) Between DMax("[DATE]","[qryLast2Readings]","[SITE]='" & [SITE] & "'") And DMin("[DATE]","[qryLast2Readings]","[SITE]='" & [SITE] & "'")))
  GROUP BY Deliveries.Site;

This worked for me - hope it does for you also!

-Russell

 

[G Marshall]

Hi Russell

That worked absolutely perfectly, just what I needed. Thanks so much for your time and interest.


Cheers



Gerald