Calculating Dates

  • Thread starter Thread starter Paul Tri via AccessMonster.com
  • Start date Start date
P

Paul Tri via AccessMonster.com

I have a report that reports on company orders and includes the following:
CompanyName,Item#, Quantity, lastorderdate, average days between orders and
next estimated order date.

"NEXT ESTIMATED ORDER DATE" is calculated by the following:

=DateDiff("d",Min([Date_ordered]),Max([date_ordered]))/Count([itemnumber])
+Max([date_ordered])

i would like to create another report to only include the companies and
what item # is estimated to be ordered within the next 30 days. I really
have no idea on where to start for this....can anyone offer any suggestions?

Thanks in advance
Paul
 
Create a new query with all the data you indicated.

Add a new column to your query with the next estimate calculation. For
example...

NextOrder: DateDiff("d",Min([Date_ordered]),...


Then in the criteria under that field put...


Between Date() and Date()+30

Then run the query to see the results, or tie it ot a report.
 
When I put that formula into the query I get the following error:

Cannot have aggregate function in WHERE clause

Am I doing something wrong here?

Thanks again!

Paul
 
Post the sql view of your query, or explain exactly what you have entered.

did you use the "NextOrder:" part? That whole line needs to go in the
FIELD: field. The Between statement needs to go in the CRITERIA: field
under that string that I posted.


--
Rick B



Rick B said:
Create a new query with all the data you indicated.

Add a new column to your query with the next estimate calculation. For
example...

NextOrder: DateDiff("d",Min([Date_ordered]),...


Then in the criteria under that field put...


Between Date() and Date()+30

Then run the query to see the results, or tie it ot a report.


--
Rick B



Paul Tri via AccessMonster.com said:
I have a report that reports on company orders and includes the following:
CompanyName,Item#, Quantity, lastorderdate, average days between orders and
next estimated order date.

"NEXT ESTIMATED ORDER DATE" is calculated by the following:

=DateDiff("d",Min([Date_ordered]),Max([date_ordered]))/Count([itemnumber])
+Max([date_ordered])

i would like to create another report to only include the companies and
what item # is estimated to be ordered within the next 30 days. I really
have no idea on where to start for this....can anyone offer any suggestions?

Thanks in advance
Paul
Click to expand...
Click to expand...
 
Id like to start by thanking you for your help with this....this is what I
have entered:

Field: NextOrder: DateDiff("d",Min([Date_ordered]),Max([date_ordered]))
/Count([itemnumber])+Max([date_ordered])

Table: Main

Sort: BLANK <--i have nothing in there.

Show: is checked

Criteria: Between Date() And Date()+30

I had left out putting the Main in TABLE on my last post but I now still
get the following error message now when I try and run the query

Extra ) in query expression
'Main.[datediff("d",Min([Date_ordered]),Max([date_ordered]))/Count(
[itemnumber])+max([date_ordered])]


thanks again!

Paul
 
Okay - I think I have misread your post.

You are calculating this on a particular record, so all the min and max
stuff can't be used. (I think)

This formula might be alittle over my head. If a MVP does not jump in here,
I will try to tackle it, but I think it might be over my head.

When I read your first post, I assumed your formula was being entered in an
unbound text box of a report. Not sure where you are using this formula?
 
I have a very similar structure in my wife's Mary Kay database. But, I have
a 'life' for all the products that determines how long it will last. I thin
find the most recent order for every product and calculate when they will
run out based on the life, the last order date, and the quantity ordered.

I then have criteria that says if that date is between now and 60 days from
now, then show it on a report.

When I first read your post, I was not paying attention to how you were
coming up with your 'next order date', I was just focussing on how to take
that figure and use it to find appropriate records.

Sorry!

--
Rick B



Rick B said:
Okay - I think I have misread your post.

You are calculating this on a particular record, so all the min and max
stuff can't be used. (I think)

This formula might be alittle over my head. If a MVP does not jump in here,
I will try to tackle it, but I think it might be over my head.

When I read your first post, I assumed your formula was being entered in an
unbound text box of a report. Not sure where you are using this formula?

--
Rick B



Id like to start by thanking you for your help with this....this is what I
have entered:

Field: NextOrder: DateDiff("d",Min([Date_ordered]),Max([date_ordered]))
/Count([itemnumber])+Max([date_ordered])

Table: Main

Sort: BLANK <--i have nothing in there.

Show: is checked

Criteria: Between Date() And Date()+30

I had left out putting the Main in TABLE on my last post but I now still
get the following error message now when I try and run the query

Extra ) in query expression
'Main.[datediff("d",Min([Date_ordered]),Max([date_ordered]))/Count(
[itemnumber])+max([date_ordered])]


thanks again!

Paul
Click to expand...
Click to expand...
 
If I understand your question, you have the first report and the other report
you want is identical to the first report except that it only shows items
expected to be ordered within the next 30 days. Wouldn't it simply be easier
to apply a filter to first report rather than creating a whole new report?
(You could save the filtered report as a new different report if you wanted
to).

I am not an expert, but it would be something like the following entered
into the reports filter property (make sure to turn the filter on):

DateDiff("d",Min([Date_ordered]),Max([date_ordered]))/Count([itemnumber])
+Max([date_ordered]) <= 30
 
RickB, thanks for all your help and attempts with this!!!!

dhgodfey, thanks for the suggestion...I believe you have put me on the
right track to figuring this out. Unfortunetly the suggestion you gave
produced an error, i would post it but i dont have access to it from where
i am.

I believe that I need to have a filter simply filtering the field where the
estimated order date is in the range of 30 days from todays date, however I
do know how to write for the filters....any suggestions would be MORE than
welcome...thanks for your time!!!

Paul
 
Back
Top