Sum in form footer (Access 2003)
Hello again. Working with Access 2003.
I'm trying to put a running Sum into a Form Footer. I put the text box in the Form Footer and created this Expression in the Control Source:
Where H0310, H0615, etc...are the Names of the fields.
Please Login or Register to view this content.
I'm getting a #Error in the new text box.
The format of each fields control source is Number and the format of the new text box is General Number.
Any ideas?
Thanks!
Last edited by WillysK5; 05-26-2009 at 04:30 PM.
Bumpity bump bump
Any help would be greatly appreciated!
Hi WillysK5,
Try removing the Sum function so the control source looks like this:
Cheers,Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
Thanks Conne, but it just leaves it blank now. There's no #Error, but there's no total either.
WillysK5
Is the form looking at an existing record (not a new record) with data in those fields?
Both. The main function is as a running total in a new form, but it doesn't do that or show up in an existing record.Originally Posted by ConneXionLost
And yes, there are existing records for it to look at...
Let me start with this is total speculation. There is no knowledge behind what I am about to say. If it sees the fields as being empty or null it won't give them a 0 value thus resulting in a Err or blank. Total guess, so take that for what it is worth, I would try the following.
Hope this helps,Please Login or Register to view this content.
Dan
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the "Star" and adding to my reputation, Thanks!
Thanks Dan, but that gives me an invalid syntax message.
I just wish I knew more about Access expressions.....
Hi WillysK5,
I built a mock-up to test the formula, and it works fine, but that isn't helping here. The only way I can reproduce this error is by disconnecting the Record Source for the form (or by removing one of the Record Source fields).
Probably a dumb question, but do you still have a Record Source connected to the form?
Cheers,
Yeah. I have one table where all the data from that one form is stored. It's named in the Record Source of that form.
I did a similar, however I got a blank when one of the fields was null, I may have screwed up the syntax. I am gonna try another thing quick.
Hi WillysK5,
Okay, really grasping at straws now; any chance you used Captions in your table and are using them instead of the Field Names in the formula?
Note - I'm running out of guesses!
Cheers,
Try that,Please Login or Register to view this content.
I just checked it with mine, no syntax error, unless one of the field names if off on yours.
Woohoo! That's it! I forgot a parenthesis.
Thanks!!
Great!
So in your table, you might want to set the default values for those fields to zero "0" instead of blank.
Cheers,
Actually, it works well without the '0' in the syntax and set as 'blank'. Were you looking at the '0's in split's first post?
What he meant was not in the form itself, or in that equation. He meant if you go to the table and select design mode, and go to the fields you are adding together, set their default value to 0. That way when you don't enter in a value they already have a 0.
All "Nz([Field])" does is if the field is "Null" then it returns 0 for the purpose of calculating. It fixes the current issue, but down the road you may have an issue you have to use Nz for if you leave them "Null".
Disclaimer, this is what I think he meant
P.S. Sorry for "hi-jacking" you helping him ConneXionLost.
Dan
That is exactly what I meant; good read split!
BTW - I don't consider what you did as "hi-jacking". I'm happy to be sharing neurons with you.
Cheers,
Desperate Need been trying to find it out for hours now!
Please help. In Access 2003 I am trying to calculate a sum of all records of a specific field. I have bound the field and unbound the field. I am able to place a specific value in the field but not able to get a sum of the values on my form footer as a total. I keep getting an
The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
There may have been an error evaluating the function, event, or macro.
Code With Bound Field to Table Field:
'Private Sub Text75_Change()
'Me.Text75.OnChange
'Me.InputParameters (Sum(ChangeOrders.PendingFundedAmount))
'End Sub
'Private Sub Text75_Click()
'Me.Text75 = "2000"
'End Sub
'Private Sub Text75_Exit(Cancel As Integer)
'Me.Text75 = "10000"
'End Sub
Private Sub Text75_Change(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.DataEntry = False
Me.Text75 = Sum([ChangeOrders].[PendingFundedAmount])
End Sub
When I try the other route unbound I am getting a sub or function not defined:
Private Sub Text75_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.DataEntry = False
Me.Text75 = Sum(Pending2FundedAmount)
End Sub
Armyguy,
Please take a few minutes to read the forum rules, and then start your own thread, with your code wrapped in code tags.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks