By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Min, Max, Avg

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. Min(Date()-[ARCHIVE]![CURRENT  ACC DE])
  2. Max: Max(Date()-ARCHIVE![CURRENT  ACC DE])
  3. Avg: Avg(Date()-[ARCHIVE]![CURRENT  ACC DE])
Thank You.
Apr 30 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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
Apr 30 '08 #2

P: 6
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
Apr 30 '08 #3

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. Min(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
  2. Max(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
  3. Avg(Forms!YourForm.NameOfTheTextbox-[ARCHIVE]![CURRENT ACC DE])
  4.  
or you could reference a non existing field and the query will ask the person running the query for its value.
Expand|Select|Wrap|Line Numbers
  1. Min([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
  2. Max([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
  3. Avg([Please Enter The Date]-[ARCHIVE]![CURRENT ACC DE])
  4.  
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
Apr 30 '08 #4

P: 6
spot on, ty for helping me.
Apr 30 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.