How to do a conditional Sum?

[prabha_friend]

=sum([Log_Hours]) gives the sum of logged Hours in a textbox. How to put =Sum([Log_Hours] WHERE [Log_date]=[Log_date])

Just to give a sum for only the selected date. How to give? Thank you.

 

[Tieval]

try DSum instead, you can have criteria with this

 

[leanpilar]

or another way is to conditioning iif before sum like

iif([Log_date]=[Log_date], then sum([Log_Hours]), null)

 

[prabha_friend]

try DSum instead, you can have criteria with this

You know how to use the form's Datasource as a Domain for the DSum?

or another way is to conditioning iif before sum like

No. I think there is a misunderstanding. I want to sum the working hours for the currently entering date, so that the entering resources will come to know How much work hours they are lagging behind on the 8 hours constant.

 

[Tieval]

Presumably the data source is a table or query and you are taking your date from a form textbox or similar so:

DSum("[Log_Hours]", "[Table Name]", "[Log_date]=[Form.Text1]")

 

[Tieval]

an example of one I use:

=DSum("Value","qryAGG","[Date]>= DateAdd('m',-3,Date()) AND [Date]<= Date()")

This gives the sum of values from a query called qryAGG where the date is greater than or equal to the date three months ago and less than or equal to todays date.

It gives me the value of orders placed in the last three months.

 

[prabha_friend]

Presumably the data source is a table or query and you are taking your date from a form textbox or similar so:

DSum("[Log_Hours]", "[Table Name]", "[Log_date]=[Form.Text1]")

SELECT Logs.*
FROM Logs
WHERE (((Logs.Log_Date) Between DateAdd("d",-1,Date()) And Date()) AND ((Logs.Log_Resource)=GetUserName()))
ORDER BY Logs.Log_Date, Logs.Log_No;

No other way than storing this as a query?

 

[Tieval]

=DSum("Value","qryAGG","[Date]>= DateAdd('m',-3,Date()) AND [Date]<= Date()")

This can be put in the Control Source of the textbox you are trying to display the data in.

 

[prabha_friend]

SumOfLog_Hours:
SELECT Sum(Logs.Log_Hours) AS SumOfLog_Hours
FROM Logs
GROUP BY Logs.Log_Resource, Logs.Log_Date
HAVING (((Logs.Log_Resource)=[Forms]![Timesheet (Regular User)]![Log_Resource]) AND ((Logs.Log_Date)=[Forms]![Timesheet (Regular User)]![Log_Date]));


[Forms]![Timesheet (Regular User)]![txtTotalHours].ControlSource=[SumOfLog_Hours]![SumOfLog_Hours]

But why am I getting a #Name? on txtTotalHours

Why can't I able to do like this:
(((Logs.Log_Resource)=Forms(1)![Log_Resource])
as I foolishly created different forms for different roles (Regular User, Admin and Super-Admin) atleast having controls with the same name

 

[Tieval]

You cannot use the result of a query in a text box as Access expects a set of results from a query. You could use this with a listbox and then specify displaying the first row of the query result but it really is making hard work of it.

 

[jandho]

=sum([Log_Hours]) gives the sum of logged Hours in a textbox. How to put =Sum([Log_Hours] WHERE [Log_date]=[Log_date])

Just to give a sum for only the selected date. How to give? Thank you.

This works for me:
=Sum(IIf([Log_date]=selecteddate, [Log_Hours], 0))

 

[Pat Hartman]

Welcome to AWF @jandho You have resurrected an 8-year old thread. It is always nice to see newcomers trying to help the rest of us