'Total'(sum) Box from a Different Record Source

[twildt]

I can't get my code to SUM correctly. I'm getting #Error in the textbox.



I have a Mainform with a textbox referencing another textbox(performing the SUM function) on a subform. I think the problem is this subform is based off a query and the field I am trying to SUM is formatted.

Here is some detailed info:

Query code:

SELECT [Work Hours].Employee, [Work Hours].[Date Worked], [Work Hours].[Work Type], [Work Hours].[Work Task], FormatMinutesAsHMM([MinutesWorked]) AS Hours, [Work Hours].[Work Descripton]
FROM [Work Hours] LEFT JOIN [Employees Extended] ON [Work Hours].Employee = [Employees Extended].ID
WHERE ((([Work Hours].[Date Worked])=Date()) AND (([Employees Extended].[User Login])=[Forms]![frmLogin]![LoginName]));

The field in question is: FormatMinutesAsHMM([MinutesWorked]) AS Hours

Subform textbox(name is totalhours) =FormatMinutesAsHMM(Sum([Hours]))
Mainform textbox =[NavigationSubform].[Form]![totalhours]

The table "Work Hours", field name "MinutesWorked", has data entered in as minutes(15, 30, 90, etc)

My FormatMinutesAsHMM module is converting those minutes into h:nn

The Subform based on the query is in datasheet view for the users so that "totalhours" textbox is in the footer.

How can I SUM this field and show it on my Mainform? Why is my current configuration displaying "#Error" instead of the SUM of the data?

[June7]

Can't sum h:nn, that is text not a number.

Need to sum the original minutes field, then format as h:nn.

[twildt]

How do I SUM original field when the record source of the subform is a query?

I build the query for the reason to filter out the data to just "today(date()". I am only returning data entered today and only today do I want the SUM field calculating.

[June7]

Shouldn't matter if the RecordSource is a table or query as long as the requisite field is included. Do the Sum in textbox in subform form footer section. If the subform is in Form or Continuous view, the footer can be displayed, if in Datasheet view then a textbox on main form can reference the textbox in the subform.

[twildt]

Shouldn't matter if the RecordSource is a table or query as long as the requisite field is included. Do the Sum in textbox in subform form footer section. If the subform is in Form or Continuous view, the footer can be displayed, if in Datasheet view then a textbox on main form can reference the textbox in the subform.

Thats what I am doing. textbox in footer of subform, then textbox on mainform referencing the textbox in subform. I'm getting that #Error.

[June7]

If you can have a field for the h:nn calculation then why can't you include the original MinutesWorked field?

[twildt]

I added the field for the MinutesWorked. I don't want this field visible to the users because it's not formatted. This is confusing to them. So if I can hide that field and still use it that would be great because now that I added that field and I am referencing it instead my SUM is working.

So how can I hide it from the users? The view is datasheet view for the users. I tried going to the design of the query and unchecking the "Show" checkbox but that removes it from the query completely.

[June7]

If there is no textbox bound to the field, then it should not show in the form but the field is still available for the Sum.

Or set the textbox Visible property to No and delete associated label.

[twildt]

That did it. I added the text box and bound it because I didn't think it would work without the text box... That was my fault. Thanks!

Resolution:
Step 1. Added another field to my query with the source data(unformatted).
Step 2. Did not create a bound text box on my subform but SUM'd the field in my footer textbox.
Now the total's textbox reference on the main form is displaying the totals from the subform footer textbox!

[June7]

Should note that doesn't always hold true with reports. Many times I tried to reference field without bound textbox and the report errors.