#Error in Form Footer Total

Hello,
I have a continuous form that has a textbox named InvoiceAmount. This
textbox is not a calculated field. It's control souce is a field also
named InvoiceAmount in the tblInvoices table. In the form footer, I
have a textbox name txtBatchTotal which should show the sum of all
invoices in the batch. The control source of txtBatchTotal is
=Sum([InvoiceAmount])

This gives me a #Error in the txtBatchTotal field.

I should mention that I'm using Access 2002 ADP with SQL 2000. Please
let me know if you have any suggestions.

Thanks.

Julie Vazquez

re: #Error in Form Footer Total

On 29 Dec 2004 16:01:11 -0800, "jv" <julie_vazquez@hotmail.com> wrote:

Check the NorthwindCS sample application, the Orders form. Its subform
has a similar setup as what you're describing. Compare carefully and
you should find the root cause.
My guess: a typo in the expression.

-Tom.

[color=blue]
>Hello,
>I have a continuous form that has a textbox named InvoiceAmount. This
>textbox is not a calculated field. It's control souce is a field also
>named InvoiceAmount in the tblInvoices table. In the form footer, I
>have a textbox name txtBatchTotal which should show the sum of all
>invoices in the batch. The control source of txtBatchTotal is
>=Sum([InvoiceAmount])
>
>This gives me a #Error in the txtBatchTotal field.
>
>I should mention that I'm using Access 2002 ADP with SQL 2000. Please
>let me know if you have any suggestions.
>
>Thanks.
>
>Julie Vazquez[/color]

re: #Error in Form Footer Total

I've checked the spelling very carefully about 10 times and have
deleted the control and recreated from scratch. The expression works
with =[InvoiceAmount], but return #Error when I put Sum() or Max()
around it like in =Sum([InvoiceAmount]). It is very strange.

re: #Error in Form Footer Total

I also found that when my form's record source is simply tblInvoices,
=Sum([InvoiceAmount]) in the form footer works fine. However, when the
form's records source is a joined query, that's when I get #error for
=Sum([InvoiceAmount]) and =Sum([tblInvoices].[InvoiceAmount]).
Does anyone know why this may be?

thanks.

re: #Error in Form Footer Total

jv wrote:[color=blue]
> I've checked the spelling very carefully about 10 times and have
> deleted the control and recreated from scratch. The expression works
> with =[InvoiceAmount], but return #Error when I put Sum() or Max()
> around it like in =Sum([InvoiceAmount]). It is very strange.[/color]

Are you sure you put this control in the form footer and not the page footer?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: #Error in Form Footer Total

I finally tried saving my form's record source Select statement as a
view and used that view as the record source. Somehow that solved my
problem. I have no idea why.....

re: #Error in Form Footer Total

On 30 Dec 2004 08:38:13 -0800, "jv" <julie_vazquez@hotmail.com> wrote:

I've noticed this too: Access ADP really likes views.
-Tom.
[color=blue]
>I finally tried saving my form's record source Select statement as a
>view and used that view as the record source. Somehow that solved my
>problem. I have no idea why.....[/color]

re: #Error in Form Footer Total

Tom van Stiphout wrote:[color=blue]
> On 30 Dec 2004 08:38:13 -0800, "jv" <julie_vazquez@hotmail.com> wrote:
>
> I've noticed this too: Access ADP really likes views.
> -Tom.[/color]

Yes it does ...
except on anniversaries of major battles of either the Punic Wars or the
Wars of the Roses
or when the distance (km) of the earth's equator from the center of the
planet Mars is a multiple of 10000
or when you have a deadline to meet and it's ... tomorrow!

On such occasions you can pretty well bet that your ADP will NOT like
Views, it will like a SPROC, or a table returning UDF, or a T-SQL script
string, and that you may require several hours to discover just which of
these, if any, it does like, and how to implement its use in precisely
the manner that the Access ADP wants.

Arggggggggggghhhhhhhhhhh! The time that Access saved me from 1992(?) to
2002 has been entirely lost in the past two years coping with Access ADP
Arcane Anomalies ... never AGAIN!

(...first rant of 2005!)

re: #Error in Form Footer Total

I thought that changing the form's record source to a view solved my
problem, but it didn't. The form total is OK until I need to refresh
or requery the form. At which point, the #error returns.

Does anyone know what's going on and how I can get rid of this problem?
Thanks.