Form Footer-Total From Query (1 Viewer)

sunnytaru

Member
Local time
Today, 14:10
Joined
Mar 24, 2022
Messages
41
Hello Experts!
I am beginner level and any guidance will be a blessing!
Just cannot figure out what went wrong as initially the form was working. The Query is returning the correct Number the issue is displaying in the Form.
I have a footer Total value that comes from a query. I tried DSum and Dlookup and I am getting either the #ERROR or #Name?

Footer Formula in one textbox ControlSource =DLookUp("SumOfOrignalBudget","WPTableFormTotalQRY","[ProjectID]= " & [Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID])

=DSum("OrignalBudget","WPTableFormTotalQRY","[ProjectID]= " & [Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID])

Query
SELECT ProjectWPTable.ProjectID, ProjectWPTable.DocumentType, Sum(ProjectWPTable.OriginalBudget) AS SumOfOriginalBudget, Sum(ProjectWPTable.CurrentBudget) AS SumOfCurrentBudget, Sum(ProjectWPTable.CurrentExpenditure) AS SumOfCurrentExpenditure, Sum(ProjectWPTable.BudgetedHours) AS SumOfBudgetedHours, Sum(ProjectWPTable.ActualHours) AS SumOfActualHours, Sum([CurrentBudget]-[CurrentExpenditure]) AS WPBudgetRemaining, Sum([BudgetedHours]-[ActualHours]) AS SumRemainHours
FROM ProjectWPTable
GROUP BY ProjectWPTable.ProjectID, ProjectWPTable.DocumentType
HAVING (((ProjectWPTable.ProjectID)=[Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID]));

Thanks
Taruna
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:10
Joined
May 7, 2009
Messages
19,169
try adding Nz to all your Criteria, example:

=DLookUp("SumOfOrignalBudget","WPTableFormTotalQRY","[ProjectID]= " & Nz([Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID], 0))


=DSum("OrignalBudget","WPTableFormTotalQRY","[ProjectID]= " & Nz([Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID], 0))


SELECT ProjectWPTable.ProjectID, ProjectWPTable.DocumentType, Sum(ProjectWPTable.OriginalBudget) AS SumOfOriginalBudget, Sum(ProjectWPTable.CurrentBudget) AS SumOfCurrentBudget, Sum(ProjectWPTable.CurrentExpenditure) AS SumOfCurrentExpenditure, Sum(ProjectWPTable.BudgetedHours) AS SumOfBudgetedHours, Sum(ProjectWPTable.ActualHours) AS SumOfActualHours, Sum([CurrentBudget]-[CurrentExpenditure]) AS WPBudgetRemaining, Sum([BudgetedHours]-[ActualHours]) AS SumRemainHours
FROM ProjectWPTable
GROUP BY ProjectWPTable.ProjectID, ProjectWPTable.DocumentType
HAVING (((ProjectWPTable.ProjectID)=Nz([Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID], 0)));
 

sunnytaru

Member
Local time
Today, 14:10
Joined
Mar 24, 2022
Messages
41
=DLookUp("SumOfOrignalBudget","WPTableFormTotalQRY","[ProjectID]= " & Nz([Forms]![WPProjectUpdateFRM]![NewProjectWPTableFRM]![ProjectID], 0))
Same Error, Thanks for response, appreciate you removing time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:10
Joined
May 7, 2009
Messages
19,169
can you use only =Sum([theFieldtoSum]) in your footer section?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:10
Joined
Feb 19, 2002
Messages
42,971
The footer on the form should not be using domain functions. The controlSource would just be:

=Sum(OrignalBudget)
 

sunnytaru

Member
Local time
Today, 14:10
Joined
Mar 24, 2022
Messages
41
The footer on the form should not be using domain functions. The controlSource would just be:

=Sum(OrignalBudget)
Initially I had tried and everything was working fine, later it went all crazy, I had to redo the database as there were issues. Now this is working same formula. thanks for the response.
 

ZKHADI

Member
Local time
Today, 23:10
Joined
Apr 5, 2021
Messages
118
why dont you make calculated fields or use query to calculate
 

Users who are viewing this thread

Top Bottom