Form Footer-Total From Query

[sunnytaru]

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]

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]

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

Same Error, Thanks for response, appreciate you removing time.

 

[arnelgp]

can you use only =Sum([theFieldtoSum]) in your footer section?

 

[sunnytaru]

can you use only =Sum([theFieldtoSum]) in your footer section?

I had tried that but not working same error

 

[sunnytaru]

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]

why dont you make calculated fields or use query to calculate