#Error in form footer

[minchazo]

I'm trying to create a Total field in the footer of a subform.

My initial form uses three boxes to narrow down what will be displayed in the subform. My subform is created off of the query qryPickList. My total field is in the footer of the subform. It's a text field named txtSubTotal. I need to sum all the txtCost fields (displayed as currency) of all the displayed records in the subform.

If I try giving it the Control Source = Sum([txtCost]) I get a #Error message.
If I try giving it the Control Source = [txtCost], it displays correctly.

In the query, txtCost is a calculated field. In the subform, I just display the results.

Any ideas how to get this to work?

Thanks,

minchazo

[Stewart Ross]

Hi. The error occurring with Sum but not on the direct reference to the same txtCost field suggests that the values in the txtCost field are actually strings, not currency types. This can happen in calculated fields if, say, Nz is used to return non-null values, or Format is used within the calculated field setting. Both these functions return string values.

You can check this by explicitly converting your txtCost value to a currency type for the sum in the footer:

=sum(CCur([txtCost]))

Let is know how you get on.

-Stewart

[minchazo]

I'm not using the Nz function nor the Format function. I double-checked my query and changed the format of the field in the query to 'general number.' I set the format of the txtCost and the txtSubTotal field to 'Currency,' all of this in the properties of the field.

I tried using the formula you suggested =sum(CCur([txtCost])), I get the same error. I tried using CDec instead of CCur and got a popup that the expression was wrong:

The expression you entered has a function containing the wrong number of arguments.

I'll try using the original values I calculated in the query to calculate my total. Anything else I should try?

[minchazo]

I got it! I referenced the value directly from the query instead of using the textbox it was being displayed in.

i.e. instead of using txtCost, I used the "Line Cost" column in the query.

=Sum([Line Cost])