Min, Max, Avg

[nathanh]

Hey all, I am looking for some help on min,max, avg, here is my code that I am using to get the min, max, avg. It is for a report that I need to submit once a year and I am looking to have it for 2007. I do know this will do the job for what I need it to do but it goes off of the current system date, and I need it to start from 01/01/2007 and end on 12/31/2007. Please if you can post an example or where to look for an example.

Min(Date()-[ARCHIVE]![CURRENT  ACC DE])
Max: Max(Date()-ARCHIVE![CURRENT  ACC DE])
Avg: Avg(Date()-[ARCHIVE]![CURRENT  ACC DE])

Thank You.

[Delerna]

what is

[ARCHIVE]![CURRENT ACC DE]

?

I'm guessing it is a field but in relation to your question, what is it?
Can you expand on what you are asking please

[nathanh]

what is

[ARCHIVE]![CURRENT ACC DE]

?

I'm guessing it is a field but in relation to your question, what is it?
Can you expand on what you are asking please

ARCHIVE is a table and CURRENT ACC DE is a field inside the table. The programming is not what I did I am trying to fix for my company and allow them to search by a past date instead of the current date. What i am looking for is the programming that will change this "Min(Date()-" to something where I can search by either a year(example 2007) or by a range of dates and still perform the same function it is now.

Thank You

[Delerna]

I still don't get what [ARCHIVE]![CURRENT ACC DE] is.
Whether its a number of days or a number of months or something else?

From the way you phrase your response I guess it dosn't really matter now.

You want to replace the Date() part of the criteria, so here are a couple of ideas.

You could have a form that has a textbox that the user types the date of interest into and then runs the query by clicking a button on that form

Min(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
Max(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
Avg(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
 

or you could reference a non existing field and the query will ask the person running the query for its value.

Min([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
Max([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
Avg([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
 

When the query is executed an input box will appear with the message

Please Enter The Date
and an input box below the message.


Or am I on the wrong track

[nathanh]

spot on, ty for helping me.