Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Get Total Amount for Year To Date
- Thread starter Happy2
- Start date
need more information on your fields but to show on a form or report just use the Dsum() function
=dsum("fieldwithamount","[tableorqueryname]","employeeid = " & me!empid & " and year(datefield) = year(date))
syntax will need to be corrected but this should give you a start.
=dsum("fieldwithamount","[tableorqueryname]","employeeid = " & me!empid & " and year(datefield) = year(date))
syntax will need to be corrected but this should give you a start.
- Thread starter
- #3
If it is returning all the dollar amounts then the problem must be in the criteria you are passing.
The Dsum function accepts these values
DSum(expr, domain[, criteria])
if I pass
dsum("fieldname","Tablename"
then it will return all the totals from the field
but if I pass it with criteria then it limits the data to whatever I pass. It is like the where statement in a query but with out the where in front
dsum("field","Table", "custid = 123"
then it will only sum all the fields where custid = 123
I can use it to pass several criteria.
what you want to pass is the employees name or id and because you want year to date you only want fields dates with this year in it. Thus year(datefield) = 2002 may be what you want.
If that is not clear enough you could try posting the statement you are using as well as the field names that are available.
The Dsum function accepts these values
DSum(expr, domain[, criteria])
if I pass
dsum("fieldname","Tablename"
then it will return all the totals from the fieldbut if I pass it with criteria then it limits the data to whatever I pass. It is like the where statement in a query but with out the where in front
dsum("field","Table", "custid = 123"
then it will only sum all the fields where custid = 123
I can use it to pass several criteria.
what you want to pass is the employees name or id and because you want year to date you only want fields dates with this year in it. Thus year(datefield) = 2002 may be what you want.
If that is not clear enough you could try posting the statement you are using as well as the field names that are available.
- Thread starter
- #5
Hi Gol4,
I have a main form called "Employee" and a subform called "Payroll".
I need to search on an employee, and it may show any of his/her year of date.
However, I need to always have a field that shows a total amount of the current
year for that selected employee. Below is the code I have so far, but somehow
it doesn't filter out the current year nor the selected employee.
=dsum("Amount","[Payroll]","employeeid = & me!employeeid & " and year(date) = year(date))
I really appreciate your help. Thanks!
I have a main form called "Employee" and a subform called "Payroll".
I need to search on an employee, and it may show any of his/her year of date.
However, I need to always have a field that shows a total amount of the current
year for that selected employee. Below is the code I have so far, but somehow
it doesn't filter out the current year nor the selected employee.
=dsum("Amount","[Payroll]","employeeid = & me!employeeid & " and year(date) = year(date))
I really appreciate your help. Thanks!
look at the criteria you passed
the first problem is
"employeeid = & me!employeeid & "
What you are doing here is building a string to pass.
The way it is written is it is looking for an employeeid that = & me!employeeid &
I'm sure there are none that match that.
the first string neeeds to look more like
"employeeid = " & me!employeeid
the nest problem is what is the name of your field that contains the date of the amount. Is it date? if so you need to change that. Date() is a function access uses to return todays date. You can overcome that by having it read as a field
"year(payroll.date) = 2002"
combined it looks like this
"employeeid = " & me!employeeid & " and year(payroll.date) = 2002"
if employeeid is a text field it needs to read
"employeeid = '" & me!employeeid & "' and year(payroll.date) = 2002"
text needs to be enclosed in qoutes, dates in pound signs. hope that helps
you can attempt to build a query that does this then copy the criteria from that.
the first problem is
"employeeid = & me!employeeid & "
What you are doing here is building a string to pass.
The way it is written is it is looking for an employeeid that = & me!employeeid &
I'm sure there are none that match that.
the first string neeeds to look more like
"employeeid = " & me!employeeid
the nest problem is what is the name of your field that contains the date of the amount. Is it date? if so you need to change that. Date() is a function access uses to return todays date. You can overcome that by having it read as a field
"year(payroll.date) = 2002"
combined it looks like this
"employeeid = " & me!employeeid & " and year(payroll.date) = 2002"
if employeeid is a text field it needs to read
"employeeid = '" & me!employeeid & "' and year(payroll.date) = 2002"
text needs to be enclosed in qoutes, dates in pound signs. hope that helps
you can attempt to build a query that does this then copy the criteria from that.
- Status
Similar threads
