display data of last 14 days

[ivanteo]

i would like to have my query display the data of the last 14 days from the last input date. is it possible?

 

[saintsman]

In the criteria add

>Date()-14

Sorry, I did not read your post correctly so please ignore me!

[This message has been edited by saintsman (edited 03-06-2002).]

 

[David R]

You can use something similar to what saintsman said, if you will know the last input date:
> DateAdd("d",-14,[Enter last input date])

Alternatively, and a bit more intuitively, you can use:
> DMax([InputDateField])-14

HTH,
David R

 

[ivanteo]

Thx for your replies!

i can retrieve data of the last 14 days from the current date but not the date entered in the [inputdatefield] of my query.
is it possible to call the date of the last input and calculate from that date?
i tried the DMax function but the last input date is required.
Any other ways?

 

[David R]

There are often other ways, but I think we need more information about your data structure and what you're trying to do.

Is [InputDateField] a field in your underlying table? You mentioned it in your query, I just wanted to be sure it wasn't a parameter or calculated field.

What you're asking to do is get all records entered (modified? do you have a separate field to track this?) in the 14 days since you last entered anything. So if you haven't entered anything since 2/28/02, you'll get data from 2/14/02-2/28/02, not 2/21/02-3/7/02. Right?

[This message has been edited by David R (edited 03-07-2002).]

 

[ivanteo]

Sorry for not being specific!
Yes the [inputdatefield] is in my underlying table and yes i want to retreive data from the 14/2 to 28/2 (assuming my last input was on the 28/2).

 

[David R]

Okay, as far as I know this requires two queries. However it's relatively painless.

1st Query:
Show the table in question, drag down only the [inputdate] field. Click the Sigma button and change Group By to Max. Save this query.

2nd Query:
This is essentially the query you already have, with all necessary fields shown. However add the query above to the shown tables. There should be NO relationship between the two. In the criteria for [inputdate], put:
>[queryMaxDateEntered].[MaxofDateModified]-14
substituting your own fields of course.

Hope that does it for you. It worked in a test database for me once I got over trying to do it all in one query.

Thanks, I learned something too.

David R


[This message has been edited by David R (edited 03-12-2002).]

 

[David R]

I should add that putting that statement in the WHERE clause of opening your form will give you a bit more flexibility, since you can 'remove filter' and see all records. I noticed your use of 'by default' in your initial posting and figured that might come in handy in case you wanted to see more than just two weeks' worth.

David R

 

[ivanteo]

Thank you so much!
i didn't think of using another query to get it. it works very fine now!

 

[David R]

I can't take credit for that, I've had it drilled into my head by the true gurus here. I'm just a conduit.

David R