Within a date range

[ptrifile]

I asked this question before and havnt found a solution, however I believe I was going about it wrong. I have a report that estimates when a companies next order date will be, I think I need to create a filter to be able to show only the companies that will order within the next 30 days from todays date. Can someone please help with this....I have been stumped for 2 days on this one....thanks for any help!!

Paul

 

[M8KWR]

use date()+30

i hope this helps

 

[ptrifile]

If i need to pull all records that are equal to or less than 30 days from today from the field text27.....how would I write that in a filter? I tried the following with no success.....

[text27]=<date()+30

What would be the correct format?

Thanks for helping!!!

Paul

 

[M8KWR]

Is it a filter on a field within a form??

If so then just right click over the field and type <date()-30 into the fitler for box

Or are you creating a command button to apply a filter to the form!!!

 

[ptrifile]

No the field is just on a report, I am not using a form to bring up the report...the report is based on a query, so I would imagine I have to do something in the query but Im not sure how to do it since the formula that calculates the estimated order date is unbound....Hope I am making sense...thanks AGAIN for your help!

Paul

 

[M8KWR]

your unbound field in the form what does a user input into this text box. If you for example enter 30 into here, as in the days.

then what you would do is put something like this into the query under your date field

<date()-[forms]![NAME_OF_FORM]![text27]

i.e if 30 is in text37, then access would read this as

<date()-30

I hope this helps, but let me know if i am missing the point of your question.

 

[ptrifile]

Thanks M8KWR, but I think we have gotten off track here and its definately my fault, I'm probably not explaining this the way I should.

There is no form. I have one report in the database already that shows the following:

Company
ItemNumber
Quantity
Last Order Date
Average Days Between Orders
Next Estimated Order Date

Now, Next Estimated Order date is calculated by the following:
=DateDiff("d",Min([Date_ordered]),Max([date_ordered]))/Count([itemnumber])+Max([date_ordered])

What I would like to do is add another report that when I run it will only show me the companies and product ID's that have an ESTIMATED ORDER DATE coming up in the next 30 days.

Im not sure if this should be done with a filter or through a query or what, I'm not very familiar with working with filters, and I just cant seem to figure this out. Hopefully this i have made myself more clear with what I have....thanks again for your time and help with this!

Paul

 

[M8KWR]

so the next estimated order date is calculated and is not a field within a table!!!!

If it is done this way, then what i would do is create a query for your first report and then you second report that you want to show orders that should be due in the next x amount of days.

In a query you can use the calculation that you have already writen.

So in a column you would have this

Next Estimated Order DateateDiff("d",Min([Date_ordered]),Max([date_ordered]))/Count([itemnumber])+Max([date_ordered])

this will create a seperate column for this field.

Then do for your second report, copy the first query, then just add <Date()-30 into the criteria under the Next Estimated Order Date.

If you are still confused it will probably be best to send me your mdb file, if that is ok with you.

The only issue is that you are not allowed to put email address into the post, so to get around this use this

kevin dot redfern at gmail dot com (hope that makes sense)