#Error in calculated control (1 Viewer)

[JamesMcS]

Afternoon all.... Happy Friday!

I've got a series of text boxes on a subform whose control sources are update using VBa, depending upon what the user has selected in some combo boxes on the main form.

The control source runs along the lines of

=avg(iif([Tagged]=True,[fieldname],Null))

but it comes up with an error.

Weird thing is, if I change the =True to =-1 or even remove it altogether in layout view, it works OK. Weirder still, if I change the code to make the control sources all feature =-1, it errors - then if you change it to =True in layout view, it works OK.... grrr! Any ideas?

 

[vbaInet]

Is tagged or fieldname calculated?

 

[JamesMcS]

Tagged is a checkbox on the form (in continuous view), fieldname points to a field in the underlying table.

It's supposed to average out the values that are selected with the check box. Other options in the combo boxes mentioned use sum instead of avg, they work OK.

 

[vbaInet]

Check that the names of the tagged checkbox and the fieldname checkbox is not the same as the field it's bound to.

 

[missinglinq]

I've seen comments, over the years, by a lot of experienced Access developers, indicating that using -1 instead of True in code is much more reliable, and noticed the same thing myself when I began developing Access apps, twelve or so years ago. Have not, however, seen anyone with an explanation as to why this is so. Just one of those quirks the Access Gnomes are prone to!

Is avg a User Defined Function? Just curious, as I can't find any reference to it up thru Access 2003, and I noticed that Intellisense didn't capitalize it as it would if it were a native Access function.

Linq ;0)>

 

[vbaInet]

I've seen that behaviour before. My only guess is that the parser probably confuses True and "True" in some rare cases. If the datatype the checkbox is bound to is Yes/No then James should be using -1/0 or Yes/No instead of True/False and not hope that the parser interprets the True as Yes. From my understanding Yes/No in Access isn't really a boolean data type. I think the parser will interpret True better if the checkbox is bound to a Byte or Integer data type. Just my thoughts, not an explanation.

You're right, avg isn't a built-in aggregate function but Avg is.

http://office.microsoft.com/en-us/a...age-of-the-values-in-a-field-HP001100399.aspx

I think he didn't copy that code directly from his control, he just typed directly on here.

 

[missinglinq]

So, you use the Avg function when writing SQL Statements, and the DAvg function (which I was familiar with) when writing VBA code, macros, and expressions. And since this is being used in VBA for a Control Source, rather than in a SQL Statement, it looks like DAvg() would work rather than Avg.

The fact that Avg is not for use in VBA code would explain why Intellisense doesn't capitalize the A, and why VBA Help doesn't get a hit on Avg.

Linq ;0)>

 

[vbaInet]

Yea, the same way as you would use Sum() or Count() in a sql statement or in the Control Source of a textbox placed in a footer. Avg works on the recordset so since he's using it in a form it should work in this case.

That's right! The search is pretty appauling really. You would have thought that entering Count in VBA would bring up DCount at the least if there was no exact match.