#Error on form. Type conversion? Please help!

[jet0l19]

Hi there.



I've been working in a form all morning trying to do a bit of manipulation. This is the general idea of what I'm trying to do:

I have one form where there is an input that you type in specifying what field you want to be used for "Amount." You can type anything in this box for example Field1, Field2, Field3, etc.

I have another form that calculates average and I want it to calculate the average of the field name that is typed in the initial box. If I type Field1 initially, I want it to average all of Field1. If I typed Field2, I'd want it to average Field2 in the same box.

Now I've gotten to the point where I can make a text box appear that says the field that I typed in by linking it to the first form in the Control Source. Now I just need to be able to change the expression to take the average of that actual field instead of just displaying the field name. I tried several things as the Control Source.

I'm basically trying to make the Control Source see Avg([Field1]), which does calculate if I type it in manually.

When I have in the Control Source

"[" & CStr([Forms]![Form Select Amount]![Amount]) & "]"

I see [Field1]

But when I try something like

Avg("[" & CStr([Forms]![Form Select Amount]![Amount]) & "]")

or

Avg("[" & CVar([Forms]![Form Select Amount]![Amount]) & "]")


I just get an #error message.


Any help would be greatly appreciated. I'm new to access and the forum so I apologize if I broke any forum etiquette.

Thank you!

[June7]

I think aggregate functions must use an actual field name that is in the RecordSource, not a variable. Reference to the control is a variable.

Also, aggregate functions should be used in header/footer sections, not Detail.

Why do you need to have user select a field? Why not just calculate all of them?

[jet0l19]

I think aggregate functions must use an actual field name that is in the RecordSource, not a variable. Reference to the control is a variable.

Also, aggregate functions should be used in header/footer sections, not Detail.

Why do you need to have user select a field? Why not just calculate all of them?

It is a pretty complex database. The user needs to select a field because the information that is important is stored in different fields for different clients. However, this number needs to be displayed on the same report for standardization purposes.

It just seems strange to me I can't just somehow convert the output which does display Field1 for me to be interpreted in a way that can be used in an expression referencing the actual Field1.

[Missinglinq]

Also, with the line


Avg("[" & CStr([Forms]![Form Select Amount]![Amount]) & "]")

you're converting Amount to a String and then you try to average it! I suspect that you can only get the average of a group of Numeric Fields, not a group of Text Fields.

The statement

...information that is important is stored in different fields for different clients...

makes me think that you may have some design flaws in your data setup.

Linq ;0)>

[June7]

I agree with Linq. Not a normalized database. That's the crux of the issue and will continue to cause issues.

Good catch on the CStr, I didn't even see it. However, removing it won't fix the problem. I tested and my remarks still apply. Using an expression in textbox ControlSource to construct the field name with a concatenated variable won't translate to the field but it can be done in VBA. Code can build the field name and set the textbox ControlSource property. I tested and it works. Example:
Private Sub Combo101_AfterUpdate()
Me.Text48.ControlSource = "=Avg([" & Me.Combo101 & "])"
End Sub