Calculating Dates

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

Just in case the user has his/her Short Date format set to other than
mm/dd/yyyy, that would be safer as

DSum("[CLUsEarned]","MyTable", _
"[Employee] = " & Me.txtEmployee & _
" AND [Completion Date] " & _
Format(DateAdd("yyyy",-2, Date()), "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Barry Gilbert" wrote in message
...
Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4
fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to
have
a text box or (not sure) field that will calculate how many CLUs have
been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that
these
community webpages have been so much help to me in the last year as I
built 4
databases. All the helpers are great!! Thank you ahead of time for any
and
all help.

But where does this formula go? Do I add a text box and use properties and
event procedure... I don't understand. I'll try it in a text box and see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

I must be doing something wrong... the text box shows #NAME? For "MyTable" I
put in the actual name of my table

"Douglas J Steele" wrote:

Just in case the user has his/her Short Date format set to other than
mm/dd/yyyy, that would be safer as

DSum("[CLUsEarned]","MyTable", _
"[Employee] = " & Me.txtEmployee & _
" AND [Completion Date] " & _
Format(DateAdd("yyyy",-2, Date()), "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Barry Gilbert" wrote in message
...
Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4
fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to
have
a text box or (not sure) field that will calculate how many CLUs have
been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that
these
community webpages have been so much help to me in the last year as I
built 4
databases. All the helpers are great!! Thank you ahead of time for any
and
all help.

Put it in the ControlSource property of a textbox, preceded by "=".

Barry

"Jan" wrote:

But where does this formula go? Do I add a text box and use properties and
event procedure... I don't understand. I'll try it in a text box and see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

Shoot Barry... I did it, here it is pasted in case I've done something stupid:
=DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & "AND
[Completion Date] #" & DateAdd("yyyy",-2,Date()) & "#")

When I go to form view to take a look, the text box reads #Name? Any ideas?

"Barry Gilbert" wrote:

Put it in the ControlSource property of a textbox, preceded by "=".

Barry

"Jan" wrote:

But where does this formula go? Do I add a text box and use properties and
event procedure... I don't understand. I'll try it in a text box and see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

Is your table actually named MyTable?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jan" wrote in message
...
Shoot Barry... I did it, here it is pasted in case I've done something
stupid:
=DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & "AND
[Completion Date] #" & DateAdd("yyyy",-2,Date()) & "#")

When I go to form view to take a look, the text box reads #Name? Any
ideas?

"Barry Gilbert" wrote:

Put it in the ControlSource property of a textbox, preceded by "=".

Barry

"Jan" wrote:

But where does this formula go? Do I add a text box and use properties
and
event procedure... I don't understand. I'll try it in a text box and
see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use
DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & "
AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form
has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I
want to have
a text box or (not sure) field that will calculate how many CLUs
have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs
this
employee has in the past 24 months. I also would like to comment
that these
community webpages have been so much help to me in the last year
as I built 4
databases. All the helpers are great!! Thank you ahead of time for
any and
all help.

Is this exactly what you put in your ControlSource? If so, you need to
replace the field names, controls, and table name with the ones in your db.
Also, my example assumes that your employee field is numeric. If it's text,
change it like so:

","[Employee] = '" & Me.txtEmployee & "' AND ...
(notice the extra single quotes?)

Barry

"Jan" wrote:

Shoot Barry... I did it, here it is pasted in case I've done something stupid:
=DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & "AND
[Completion Date] #" & DateAdd("yyyy",-2,Date()) & "#")

When I go to form view to take a look, the text box reads #Name? Any ideas?

"Barry Gilbert" wrote:

Put it in the ControlSource property of a textbox, preceded by "=".

Barry

"Jan" wrote:

But where does this formula go? Do I add a text box and use properties and
event procedure... I don't understand. I'll try it in a text box and see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.

Barry, I'm a mess... =( Here is how I have it now.. however, you said to
preceed it with an equal sign but when I do it tells me I have too many
arguments:
DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "'AND [Completion Date] #" & DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#")
FYI: CLUs Earned is the name of a field in the Mandatory Continued Learning
Units Table, SSN is what I use to relate all the tables so I can track all
information in various forms and completion date is the name of another field
in Mandatory Cont... Units Table. Still returning #Name? What do you think
now?


"Barry Gilbert" wrote:

Is this exactly what you put in your ControlSource? If so, you need to
replace the field names, controls, and table name with the ones in your db.
Also, my example assumes that your employee field is numeric. If it's text,
change it like so:

","[Employee] = '" & Me.txtEmployee & "' AND ...
(notice the extra single quotes?)

Barry

"Jan" wrote:

Shoot Barry... I did it, here it is pasted in case I've done something stupid:
=DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & "AND
[Completion Date] #" & DateAdd("yyyy",-2,Date()) & "#")

When I go to form view to take a look, the text box reads #Name? Any ideas?

"Barry Gilbert" wrote:

Put it in the ControlSource property of a textbox, preceded by "=".

Barry

"Jan" wrote:

But where does this formula go? Do I add a text box and use properties and
event procedure... I don't understand. I'll try it in a text box and see.

"Barry Gilbert" wrote:

Not sure if I'll get the fields correct, but I think you want to use DSum:
DSum("[CLUsEarned]","MyTable","[Employee] = " & Me.txtEmployee & " AND
[Completion Date] #" & DateAdd("yyyy",-2, Date()) & "#")

HTH,
Barry

"Jan" wrote:

I have a database to track employee required training. One form has 4 fields:
course, completion date, CLUs Earned, CLUs in past 24 months. I want to have
a text box or (not sure) field that will calculate how many CLUs have been
earned in total during the past 24 months only. So, when I open my
switchboard, the main form Employees, will display how many CLUs this
employee has in the past 24 months. I also would like to comment that these
community webpages have been so much help to me in the last year as I built 4
databases. All the helpers are great!! Thank you ahead of time for any and
all help.