unresolved problem

[chivo123]

Hey all,


I have posted a problem 6 days and I still haven't got any answer back.
Open up the database faculty ---> click on faculty Details --> then page 294.

On that form --> what I want to do when I choose agent name on the top, and select the dates, it will populate me with the average of the span date.

For instance, when I click on Test, and I want it to get the AVG of date from Dec 20th-- Dec 23rd. somehow when I put this formula (=IIf(Int(DateDiff("d",[Date],[End Date]))>1,Sum([Average Wrap]),"Siavash"), all functions work, but it doesn't return me the sum average Wrap.

When I put this formula and I select difference date of more than 2, it returns me the value of multipication.
=IIf(Int(DateDiff("d",[Date],[End Date]))>1,5*10,"Siavash")

any solutions please????

[Rainlover]

How can
"all functions work, but it doesn't return me the sum average Wrap."

when it doesn't return what you want.


Is the following a different question.

When I put this formula and I select difference date of more than 2, it returns me the value of multipication.
=IIf(Int(DateDiff("d",[Date],[End Date]))>1,5*10,"Siavash")

It would help if you sample was in 2003 as not everyone has 2010.

[chivo123]

I'm not sure why it returns two values, but I think subreport always shows two values. I'm not sure what you mean about 2003. This is just a test table that I have posted. My other database will have at least 100000 data in there. So, it's better to have all in 2010.

[alansidman]

Save your test database as a 2003 database for uploading to the forum. You will get more people to look at it. Many of the experienced users on this forum (and others) still run AC 2003. And you really do not want to exclude them from helping you.

Alan

[chivo123]

Here is the 2003 format. Thank you Alan for mentioning it. Let me know if it works. My computer wouldn't let me to save it as 2003.. I had to change the extension to mdb.

[Rainlover]

You can't convert a Database by simply changing the extension.

You need to find "Save As" It is in the top left corner somewhere.

Sorry for being vague but I do not have 2010.

[chivo123]

I know that, but my save as doesn't give me any options for the types to save it as. Also, the database has some of tables that is designed only for 2007 and higher version. It won't even let me to convert to 2003.

[alansidman]

You have probably used some features in AC2010 that are not backward compatible. You will need to figure out which ones these are and disable them.

Here is the MS KB on the issue.

http://office.microsoft.com/en-us/ac...010341553.aspx

[chivo123]

I have no clue what to change in there. I have got the template from access and filled out the info from there. Is there a way to return an average of something from selecting span of begin date and end date on subform or not?

[Rainlover]

An Average is possible.

What I can't understand is your Structure so that is why I need 2003.

Maybe someone with 2010 can help.

[Michael Dean]

The problem is missing format settings for "Average Wrap" in query1 and on the form.

On Table1, [Total outbound Talk Time] and [Total Inbound Talk Time] are defined as data type Date/Time, with a format of h:nn:ss

There are no format specifications for the field [Average Wrap], which is a calculated field created in query1. On the Property Sheet of Field Properties in query1, enter a format of h:nn:ss for [Average Wrap]. On the Property Sheet for the [Average Wrap] text box on that form that appears on Page 294, also set the Format to h:nn:ss.

The attached includes those changes. Let me know if it works for you.

[alansidman]

Here is information on why you cannot convert you db.

http://office.microsoft.com/en-us/ac...010341553.aspx

and this one is very useful

http://blogs.office.com/b/microsoft-...roadblock.aspx

Alan

[chivo123]

Hey Dean,
The database you send me doesn't return the calculation for average. You know when you go to Open up the database faculty ---> click on faculty Details --> then page 294. It doesn't display the average.

[Michael Dean]

Odd. It works for me. Although there are things I don't understand going on here. The page 294 form Data Entry property is set to No, and yet every time I open it there are no records. Each new test record I enter calculates [Average Wrap] correctly, and these records are being stored in Table1. Try opening query1. Do you see all the test data there, and what is happening in that calculated field? It's calculated correctly for me there as well.

[chivo123]

Hello Dean,
I can calculate daily averages. That is very simple. I need to be able to calculate AVG for each agent name, and the AVG has to be on span of dates. For instance,

I choose Test as my agent name..
next I choose starting date and ending date.
Next, it will calculate the difference between those date and it will take the average.

What you have done is calculate daily average.