Min Max Dates and Values

[natep26]

I have a query that has InspID, InspDate, NextInspDate, Emissions (calculated by multiplying timeelapsed by associated InspDate Result)


I have another query based off this query that sums the Emissions between two selected dates. This works fine as long as the dates I select are actual InspDates which are random. However if I want a clean sum period (Jan 1, 2007-Dec 31,2007) It only sums the emissions that have an Inspdate after Jan 1. Therefore, if I have an inspection on Dec 28, 2006 and Jan 5, 2007, the query will not count any emisisons for Jan 1-5.

When this happens I need the query to lookup the [result] value of the previous InspDate and multiply it by the [timeelapsed] between the [selecteddate] and the [NextInspDate].

What is the best way to retrieve this information?

Thanks

 

[statsman]

In the criteria in the InspDate of your query enter the following:

Between >=[Start Date] And <= [End Date]

When you run the query it will prompt you for a start date and end date. Everything between the two dates should be returned.

 

[boblarson]

In the criteria in the InspDate of your query enter the following:

Between >=[Start Date] And <= [End Date]

When you run the query it will prompt you for a start date and end date. Everything between the two dates should be returned.

Sorry to have to run a correction again. You EITHER use

>=[Start Date] And <=[End Date]

or

Between [Start Date] And [End Date]

And you might have to encapsulate each date with # signs.

The Between syntax is actually INCLUSIVE so if the End Date is 1/5/2008 and you put in 1/5/2008 it will pull dates of 1/5/2008 UNLESS you have your date field using time too. Then, it will NOT necessarily be inclusive as the time you have in the date field (even if you can't see it) will have to equal midnight exactly for it to show up or you would have to put in 1/6/2008 12:00:00 AM as the date to get dates of 1/5/2008 to show up.

Hope that helps.

 

[natep26]

I am already able to use the Between function to retrieve the information between inspection dates. However inspection dates can be from 1 hour to 33 days apart. So, if I just want to know the emissions for 1 month (jan1-Jan31)the returned value will actually be from Jan1-Feb 3 (in the case of an inspection being done on Jan 1 and the next inspection on Feb 3).

So I need to find a way to calculate the emissions between actual inspection dates.

Does this make sense? Thanks for your interest so far.

 

[natep26]

I'm going to try again. I have a query with InspDate and lbsperhour.

I need to be able to insert any StartDate and EndDate and get the sum of emissions. For start/end dates not associated with an actual insp date, I need to assume the value of the previous inspection for however many days until the next insp.

Example

Jan 11 35
Feb 14 26
Feb 25 15

Sum emissions from Feb 12-Feb 27.

35*2days (feb14-Feb12)+26*11days (feb25-Feb14)+15*2days (feb27-Feb25)

This is dummed down quite a bit. There are other variable that go into the calculation. The part I'm having trouble with is the "previous result * datediff". My query is currently set up to find the previous and next inspection and find the days in between.

 

[Brianwarnock]

I believe that his will need VBA code as there is a number of possibilities for the logic flow to cater for.

It must read the file in inspdate order until Inspdate > or = to startdate

If > backtrack to get emissions from previous inspdate and apply to time frame next inspdate –startdate, unless next inspdate >= endate then apply to all time frame, else.. well it goes on and thus programming logic is needed.

The start and end dates may span several inspection dates or none so your logic flow has to allow for all possibilities.

Brian

 

[natep26]

My first question is where would I put the VBA code and the second question is can you point me in the right direction or at least a source that can help out.

Thanks