Access report problem

[hyman]

Final - forum.zip

Why cannot function DAvg work when data type of the field, which is applied within the "criteria", is combo box (mutiple choices are allowed)?


It works well when data type of the applied field is text or combo box (mutiple choices are not allowed)?
See the error in attached report "overall rating"!

[JoeM]

Take a look at Aggregate (Totals) Functions in Access's built-in help files. These allow you to group records on certain fields, and then do a mathematical computation on those groups (like Sum, Count, Average, Min, Max, etc).

[hyman]

I tried with the function of DAvg. But it did not work with the "criteria" part.
Here is my function:
=DAvg("[Warehouse]![Footprint overall site]";"Warehouse";"[Warehouse]![Industry]=' " & [Industry] & " ' ")

ps. "Warehouse" is the table name. Now I am creating the function under a report. The field of "Industry" is a combo box, whose choices are retrieved from the table of DataDictionary.

It works well work when replacing "Industry" by normal text field.
How should I rephrase expression of the bold part?

[JoeM]

The field of "Industry" is a combo box, whose choices are retrieved from the table of DataDictionary

Where exactly is this "Industry" combo box found? I think you need to qualify it, with where it is coming from.

[hyman]

It is from the table of "Warehouse". I create my report based on this table.

[JoeM]

Is it a single record table?
If not, how does the formula know which value for "Industry" to use?

[hyman]

No, it is not a single record table! But I include the field of "Industry" in the report. So the formula knows which value for "Industry" to use.

The key of the issue is: data type of the field of "Industry".
The current data type of the field of "Industry" is combo box (multiple choices).
If I set data type as normal text and input content myself (instead of selecting), my formula works well.

So how realize my goal with data type of combo box?

[JoeM]

If I set data type as normal text and input content myself (instead of selecting), my formula works well.

I think you are still leaving out some important details. A Report is not editable, so you cannot make a Combo Box selection on a Report.
You can on a Form, though. Do you have some sort of selection Form that you are using to make selections to run your Report?
If so, what does your VBA code look for that?

Also, which section of the report are you placing this average calculation in?

[hyman]

I uploaded the file. I think it will explain my problem. Sorry for the confusion.

[JoeM]

Unfortunately, I cannot download files from my current location (corporate security policy). If you can explain what is going on, I may be able to help you sort it out.

[hyman]

Sorry for my late reply.
I use below formula on my report:
=DAvg("[Warehouse]![Footprint overall site]";"Warehouse";"[Warehouse]![Industry]=' " & [Industry] & " ' ")

"Warehouse" is the name of my table. "Industry" is a field of my table. Data type of "Industry" is Combo box. If multiple values are not allowed within this Combo box, my formula works well. However, it does not work if multiple values are allowed within this Combo box.
Unfortunately, multiple values have to be allowed with my database. So I would like to know how I should rephrase my formula.

[JoeM]

But where is this combo box located?
What do you mean when your say it can have multiple-values? Did you set it to be a multi-valued field (see here: http://office.microsoft.com/en-us/ac...010149297.aspx)?
If it is a multi-valued field, I do not think I can help you. I avoid those pretty much at all costs, and have never used them. To me, they just seem to make things a lot harder than they have to be.

[hyman]

Yes, it is a multi-valued field. I guess I should set the field differently!
Anyway! Thank you for your patient replies!