Help with Sum function

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I have a subform in a form. the subform has an "Amount" field that I need to
sum up in a text box at the bottom of the form. I tried this line of code
but, it is not working. I don't know what I am doing wrong.

=Sum(Form![Invoice Tracker subform1].[Invoice Amount])

Any help will be greatly appreciated.
Thank you.
Ayo
 
Ayo,

In the Footer section of the subform, put an unbound textbox, and in the
Control Source enter like this:
=Sum([Invoice Amount])
Let's say you name this textbox TotalAmount.

If you need this total amount to be shown on the main form, rather than
the subform, then you can set its Visible property to No in the subform,
and then on the main form, use a textbox with this Control Source:
=[Invoice Tracker subform1]![TotalAmount]
 
Thank you so much. That worked great. Any idea what's wrong with this code?

=DLookUp("[Address]","[Vendors]","[Vendor Name]=Forms![txtVendor]") & ", " &
DLookUp("[City]","[Vendors]","[Vendor Name]=Forms![txtVendor]") & ", " &
DLookUp("[State]","[Vendors]","[Vendor Name]=Forms![txtVendor]") & " " &
DLookUp("[Zip]","[Vendor]","[Vendor Name]=Forms![txtVendor]")

I am tring to concantenate Address, City, State Zip into a textbox based on
the value in another textbox txtVendor.

Steve Schapel said:
Ayo,

In the Footer section of the subform, put an unbound textbox, and in the
Control Source enter like this:
=Sum([Invoice Amount])
Let's say you name this textbox TotalAmount.

If you need this total amount to be shown on the main form, rather than
the subform, then you can set its Visible property to No in the subform,
and then on the main form, use a textbox with this Control Source:
=[Invoice Tracker subform1]![TotalAmount]

--
Steve Schapel, Microsoft Access MVP
I have a subform in a form. the subform has an "Amount" field that I need to
sum up in a text box at the bottom of the form. I tried this line of code
but, it is not working. I don't know what I am doing wrong.

=Sum(Form![Invoice Tracker subform1].[Invoice Amount])

Any help will be greatly appreciated.
Thank you.
Ayo
Click to expand...
Click to expand...
 
Ayo,

The syntax "Forms![txtVendor]" is not correct. If you are referring to
an external form, then you would name the form, like this:
[Forms]![NameOfForm]![txtVendor]

However, it sounds like you are referring to a control on the same form.
So you could do it like this:
=DLookUp("[Address]","[Vendors]","[Vendor Name]='" & [txtVendor] & "'")
& ", " &
DLookUp("[City]","[Vendors]","[Vendor Name]='" & [txtVendor] & "'") & ",
" &
DLookUp("[State]","[Vendors]","[Vendor Name]='" & [txtVendor] & "'") & "
" &
DLookUp("[Zip]","[Vendor]","[Vendor Name]='" & [txtVendor] & "'")

To simplify it, you can change it to this:
=DLookUp("[Address] & ', ' & [City] & ', ' & [State] & ' ' &
[Zip]","Vendor","[Vendor Name]='" & [txtVendor] & "'")
 
Back
Top