Date\Time period between last record in table and given Time interval

[AlLeX_Brd]

HI all,
I am still new in Access databases

I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().

When this is done I have create calculations with extracted records using agregate functions.

I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.

Please help!

 

[Jon K]

In a Totals query, you can select Where in the Total row and use the DMax() function in the criteria for the date field:-

Between DMax("DateValue([DateFieldName])","TableName")-10 And DMax("DateFieldName","TableName")
.

 

[AlLeX_Brd]

In a Totals query, you can select Where in the Total row and use the DMax() function in the criteria for the date field:-

Between DMax("DateValue([DateFieldName])","TableName")-10 And DMax("DateFieldName","TableName")
.

Thanks, I'll try it!

 

[AlLeX_Brd]

I think is working but Date column disappeared

 

[EMP]

It's not clear what you meant by "Date column disappeared". Jon's Where expression, in fact any Where expression, affects only record selections, not the display of columns.


I have attached a database, which contains a Totals Query group by Category and Date:-

SELECT TableName.Category, DateValue([DateFieldName]) AS [Date], Count(TableName.Amount) AS CountOfAmount, Sum(TableName.Amount) AS SumOfAmount
FROM TableName
WHERE (((TableName.DateFieldName) Between DMax("DateValue([DateFieldName])","TableName")-10 And DMax("DateFieldName","TableName")))
GROUP BY TableName.Category, DateValue([DateFieldName]);

When the query is run, it returns these results including a Date column:-

[b]Category	[color=blue]Date[/color]	CountOfAmount	SumOfAmount[/b]
C001	   6/17/2005		2	     $10.00
C001	   6/18/2005		2	     $12.00
C002	   6/19/2005		2	     $14.00
C002	   6/20/2005		2	     $16.00
C002	   6/21/2005		2	     $18.00
C003	   6/22/2005		2	     $20.00
C003	   6/23/2005		2	     $22.00
C003	   6/24/2005		2	     $24.00
C004	   6/25/2005		2	     $26.00
C004	   6/26/2005		2	     $28.00
C004	   6/27/2005		2	     $30.00

Since the 'General Date' field contains also the times, you need to use the DateValue() function to extract the dates.

^

 

[AlLeX_Brd]

'Disappeared column' is fixed.

What now troubles me is when I use the function given to me from JonK(I'm very grateful to him) returned records slightely differ from what I expected.

If last Date is 06-Apr-2005 7:00:00 PM when I execute the query(10 days prior) the first record is
27-Mar-2005.It shoud be 27-Mar-2005 7:00:00 PM

 

[EMP]

If you need to compare the time too, try deleting the DateValue() function, that is

Between DMax("DateFieldName","TableName")-10 And DMax("DateFieldName","TableName")

^

 

[AlLeX_Brd]

If you need to compare the time too, try deleting the DateValue() function, that is

Between DMax("DateFieldName","TableName")-10 And DMax("DateFieldName","TableName")

^

Yes, Now is working, the way it should do!
Thanks a lot!