last record -first record

[510sx]

I think I spent enough time for this problem. I feel like I am spinning the wheel and getting nowhere. I hope you guys can help me.

What I intented to do is to query how much water pumped out of each pump within a given datetime range. Pretty simple right? You take the last record minus the first record of the date time range you entered, right?

Here is my query in sql view

SELECT Abs(Last(Tran_Lift1_Acre_Ft.Tran_Lift1_Pump1)-First(Tran_Lift1_Acre_Ft.Tran_Lift1_Pump1)) AS [Pump 1 Acre-Ft]
FROM Tran_Lift1_Acre_Ft
WHERE (((Tran_Lift1_Acre_Ft.DateNTime) Between [Forms]![DateTime Parameters]![StartDate] And [Forms]![DateTime Parameters]![EndDate]));

I found this query works only if I enter a short date range (from yesterday to today) If I query from last week to today, it will give me the wrong number.

I've tried to strip down my query to

SELECT Last([Tran_Lift1_Acre_Ft].[Tran_Lift1_Pump1]) AS [Pump 1 Acre-Ft]
FROM Tran_Lift1_Acre_Ft
WHERE (((Tran_Lift1_Acre_Ft.DateNTime) Between [Forms]![DateTime Parameters]![StartDate] And [Forms]![DateTime Parameters]![EndDate]));


it would still give the wrong answer.

Note: my datetime format is like this 9/27/2007 11:44:58 PM same as the datetime format is my table.

any idea?

 

[neileg]

Last and first don't work the way you expect. A table is an unordered dataset. The records may not be in the order you expect. Even when you sort a table, you only sort the display, not the underlying data. So if the table contents are 'jumbled up' the Last and First records may not be the ones you see.

You have two choices to get around this
1) Use a query and apply the sort to the query, then use last or first.
2) Use Min or Max. These are independant of sort order.

 

[510sx]

I've thought about using Min/Max, but I could see a pontential issue. if there is a record reads 0, then my min would be 0 within the datetime range.

I haven't tried to use min/max on my date time stamp column, though.

 

[Pat Hartman]

You need to use min/max on the date field.

BTW, a recent discussion in the MVP newsgroup brought up the point that first and last still do not work as anticipated even if the query is sorted.

 

[neileg]

Pat, I've not had a problem where I've applied the sort in a query and used that to feed a second query using first or last, instead of applying the sort and the aggregate function in the same query. Have I just been lucky?