Can't use aggregate functions on calculated controls? 1

[johnnygeo]

I've found a few old posts related to this, but no unequivocal explanation:

I have a simple continuous form based on a table with an integer field [myInt]. I made a control "myInt" with source [myInt]. In the form header, I made another control "mySum" with source =sum([myInt]). That works just fine.

Now I add a second control "myInt2" to the form detail, with source =[myInt]. This just repeats exactly what is in the "myInt" control. Again, works fine.

Finally, I add a second control "mySum2" to the form header with source =sum([myInt2]). This control returns #Error. Not only that, but the original "mySum" control *also* returns #Error.

Can someone explain why aggregate functions don't seem to work on calculated controls?

 

[randy700]

The source =sum(myInt2) refers to a non-existant field (myInt2). Basically, you are not adding the values of the text boxes, you are adding the values of the fields. If you made the source of your myInt2 control =sum(myInt), as you did with the first one, everything would work just fine.



Randy

 

[johnnygeo]

Thanks, Randy, but what if I *want* to sum a series of text boxes, rather than a source field? Is this not possible? It seems logical enough to me.

 

[TheAceMan1]

How are ya johnnygeo . . . . .

[blue]Aggregate Functions[/blue] were designed for [blue]SQL[/blue]. In fact, if you search in help for the Sum Function it'll come under the heading of [blue]SQL Aggregate Functions[/blue]. As such, these functions can only work on [blue]Bound Fields[/blue] in a Query/SQL. [purple]Your trying to sum an unbound field . . . .[/purple] The reason both sums fail is because the [blue]Micfosoft Jet[/blue] sets up all aggregates before their execution, particulary looking fo dependicies (as with Sum(myInt2)). When it comes across your unbound sum, its a . . . [purple]No Can Do[/purple] . . . [purple]All Stops Here[/purple] . . . [purple]#Error[/purple] situation. In the help for [purple]#Error[/purple], the first listing relates directly to your problem:

[blue]Make sure that the field specified in the control's ControlSource property hasn't been removed from the [purple]underlying table[/purple] or other [purple]record source[/purple].[/blue]

[purple]myInt2[/purple] in not in a table . . . .

To circumvent this then, you have to somehow [blue]bind[/blue] myInt2. You have two options:
[ol][li]Add a field to the table.[/li]
[li]Base the [blue]RecordSource[/blue] of the form on a query/sql and add a [purple]custom field[/purple].[/li][/ol]

Here are the steps for 2 above:
[ol][li]Design a query to return the fields you currently have.[/li]
[li]While in Query Design View, add the following line to the first blank field on the Field Row:

[blue] myInt2:myInt[/blue]

[/li]
[li]Save/Name the query.[/li]
[li]Open the form in [blue]design view[/blue] and set the Recordsource to the query name.[/li]
[li]Delete the myInt2 TextBox.[/li]
[li]Drag myInt2 backout from the field list.[/li][/ol]

[blue]Thats it . . . . give it a whirl andlet me know . . . .[/blue]

See Ya! . . . . . .

 

[loneranger27]

Hi,

I am having a similar problem but different,

I have a form that calculates a number based on adding items in a table.

When I run this program on some windows enviroments I get the #error in the text boxes doing the math. BUT when I reinstall the windows enviroment it works fine. I mostly have this problem with windows 2000. Anyone have any ideas?

Thanks

 

[TheAceMan1]

How are ya loneranger27 . . . . .

I'd like to help ya but this is [blue]johnnygeos[/blue] thread, and [purple]its not fair[/purple] to him to side step and mix-up two problems in one.

Start your own thread. I'm sure, one of many guru's we have here will pick it up! ;-)

See Ya! . . . . . .

 

[johnnygeo]

Thanks TheAceMan1. That is exactly the explanation I was seeking.