Calculating Dates

Ay, there's the rub. DSum will only work against a numeric field. If you're
storing text representations of numbers, you can modify it thusly:

=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

I actually have CLUs as text... spelling is correct.

"Barry Gilbert" wrote:

Jan,
Let's make sure the field types are correct.
CLUs Earned = numeric
SSN = Text
Completion Date = Date

Also, double-check the spelling of everything.

Barry

"Jan" wrote:

I can't believe it... I've never had this much trouble seeing my mistake!!!
It just is not working.. still coming up with #Name?
Do I need to have a field in a table in order to add this control in the
form? I'm just adding a text box to the form and pasting the strand in
control source.

"Barry Gilbert" wrote:

Let this be a lesson to all of you
space-within-table-name-or-field-name-putting developers out there! Good
catch.

"Douglas J. Steele" wrote:

Actually, square brackets are required around the table name, because of the
spaces in its name:

=DSum("[CLUs Earned]","[Mandatory Continued Learning Units]","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")


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


"Barry Gilbert" wrote in message
...
Ok. Let's simplify it a bit by taking out the format statement:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

Yes, it is a text field... I have an input mask though.. for a SSN.
Jan

"Barry Gilbert" wrote:

Jan,

Please confirm that the SSN field in your table is a text field.
Barry

"Jan" wrote:

I'm still getting #Name? Here it is:
=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),"\#mm\/dd\/yyyy\#"))
What is wrong now?
Jan

"Barry Gilbert" wrote:

If you plan to use Doug's suggestion, you left off the Format
function:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#"))

Barry

"Jan" wrote:

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.

I'm beginning to think this is impossible!! OK.. copied that strand and it
tells me the expression contains invalid syntax:
=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" & Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")
Shall I change CLUs Earned to a numeric field?
Jan


"Barry Gilbert" wrote:

Ay, there's the rub. DSum will only work against a numeric field. If you're
storing text representations of numbers, you can modify it thusly:

=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

I actually have CLUs as text... spelling is correct.

"Barry Gilbert" wrote:

Jan,
Let's make sure the field types are correct.
CLUs Earned = numeric
SSN = Text
Completion Date = Date

Also, double-check the spelling of everything.

Barry

"Jan" wrote:

I can't believe it... I've never had this much trouble seeing my mistake!!!
It just is not working.. still coming up with #Name?
Do I need to have a field in a table in order to add this control in the
form? I'm just adding a text box to the form and pasting the strand in
control source.

"Barry Gilbert" wrote:

Let this be a lesson to all of you
space-within-table-name-or-field-name-putting developers out there! Good
catch.

"Douglas J. Steele" wrote:

Actually, square brackets are required around the table name, because of the
spaces in its name:

=DSum("[CLUs Earned]","[Mandatory Continued Learning Units]","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")


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


"Barry Gilbert" wrote in message
...
Ok. Let's simplify it a bit by taking out the format statement:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

Yes, it is a text field... I have an input mask though.. for a SSN.
Jan

"Barry Gilbert" wrote:

Jan,

Please confirm that the SSN field in your table is a text field.
Barry

"Jan" wrote:

I'm still getting #Name? Here it is:
=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),"\#mm\/dd\/yyyy\#"))
What is wrong now?
Jan

"Barry Gilbert" wrote:

If you plan to use Doug's suggestion, you left off the Format
function:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#"))

Barry

"Jan" wrote:

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.

If CLUs Earned will only contain numbers, you'd be much better off making it
a numeric field.

Barry

"Jan" wrote:

I'm beginning to think this is impossible!! OK.. copied that strand and it
tells me the expression contains invalid syntax:
=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" & Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")
Shall I change CLUs Earned to a numeric field?
Jan


"Barry Gilbert" wrote:

Ay, there's the rub. DSum will only work against a numeric field. If you're
storing text representations of numbers, you can modify it thusly:

=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

I actually have CLUs as text... spelling is correct.

"Barry Gilbert" wrote:

Jan,
Let's make sure the field types are correct.
CLUs Earned = numeric
SSN = Text
Completion Date = Date

Also, double-check the spelling of everything.

Barry

"Jan" wrote:

I can't believe it... I've never had this much trouble seeing my mistake!!!
It just is not working.. still coming up with #Name?
Do I need to have a field in a table in order to add this control in the
form? I'm just adding a text box to the form and pasting the strand in
control source.

"Barry Gilbert" wrote:

Let this be a lesson to all of you
space-within-table-name-or-field-name-putting developers out there! Good
catch.

"Douglas J. Steele" wrote:

Actually, square brackets are required around the table name, because of the
spaces in its name:

=DSum("[CLUs Earned]","[Mandatory Continued Learning Units]","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")


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


"Barry Gilbert" wrote in message
...
Ok. Let's simplify it a bit by taking out the format statement:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

Yes, it is a text field... I have an input mask though.. for a SSN.
Jan

"Barry Gilbert" wrote:

Jan,

Please confirm that the SSN field in your table is a text field.
Barry

"Jan" wrote:

I'm still getting #Name? Here it is:
=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),"\#mm\/dd\/yyyy\#"))
What is wrong now?
Jan

"Barry Gilbert" wrote:

If you plan to use Doug's suggestion, you left off the Format
function:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#"))

Barry

"Jan" wrote:

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.

Barry, It says the invalid syntax thing and then this "You many have entered
a comma without a preceeding value or identifier." Can you see the problem?
Jan

"Barry Gilbert" wrote:

Ay, there's the rub. DSum will only work against a numeric field. If you're
storing text representations of numbers, you can modify it thusly:

=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

I actually have CLUs as text... spelling is correct.

"Barry Gilbert" wrote:

Jan,
Let's make sure the field types are correct.
CLUs Earned = numeric
SSN = Text
Completion Date = Date

Also, double-check the spelling of everything.

Barry

"Jan" wrote:

I can't believe it... I've never had this much trouble seeing my mistake!!!
It just is not working.. still coming up with #Name?
Do I need to have a field in a table in order to add this control in the
form? I'm just adding a text box to the form and pasting the strand in
control source.

"Barry Gilbert" wrote:

Let this be a lesson to all of you
space-within-table-name-or-field-name-putting developers out there! Good
catch.

"Douglas J. Steele" wrote:

Actually, square brackets are required around the table name, because of the
spaces in its name:

=DSum("[CLUs Earned]","[Mandatory Continued Learning Units]","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")


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


"Barry Gilbert" wrote in message
...
Ok. Let's simplify it a bit by taking out the format statement:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

Yes, it is a text field... I have an input mask though.. for a SSN.
Jan

"Barry Gilbert" wrote:

Jan,

Please confirm that the SSN field in your table is a text field.
Barry

"Jan" wrote:

I'm still getting #Name? Here it is:
=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),"\#mm\/dd\/yyyy\#"))
What is wrong now?
Jan

"Barry Gilbert" wrote:

If you plan to use Doug's suggestion, you left off the Format
function:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#"))

Barry

"Jan" wrote:

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.

Jan,

I've created a table and form matching your description. When I try this
function, it works as expected. Have you changed the datatype of CLUs Earned
to Integer? If so, you can leave off the CInt function.

Barry

"Jan" wrote:

Barry, It says the invalid syntax thing and then this "You many have entered
a comma without a preceeding value or identifier." Can you see the problem?
Jan

"Barry Gilbert" wrote:

Ay, there's the rub. DSum will only work against a numeric field. If you're
storing text representations of numbers, you can modify it thusly:

=DSum("CInt([CLUs Earned])","[Mandatory Continued Learning Units]","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

I actually have CLUs as text... spelling is correct.

"Barry Gilbert" wrote:

Jan,
Let's make sure the field types are correct.
CLUs Earned = numeric
SSN = Text
Completion Date = Date

Also, double-check the spelling of everything.

Barry

"Jan" wrote:

I can't believe it... I've never had this much trouble seeing my mistake!!!
It just is not working.. still coming up with #Name?
Do I need to have a field in a table in order to add this control in the
form? I'm just adding a text box to the form and pasting the strand in
control source.

"Barry Gilbert" wrote:

Let this be a lesson to all of you
space-within-table-name-or-field-name-putting developers out there! Good
catch.

"Douglas J. Steele" wrote:

Actually, square brackets are required around the table name, because of the
spaces in its name:

=DSum("[CLUs Earned]","[Mandatory Continued Learning Units]","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")


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


"Barry Gilbert" wrote in message
...
Ok. Let's simplify it a bit by taking out the format statement:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] = '" &
Me.txtSSN & "' AND [Completion Date] #" & DateAdd("yyyy",-2,Date()) &
"#")

Barry

"Jan" wrote:

Yes, it is a text field... I have an input mask though.. for a SSN.
Jan

"Barry Gilbert" wrote:

Jan,

Please confirm that the SSN field in your table is a text field.
Barry

"Jan" wrote:

I'm still getting #Name? Here it is:
=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),"\#mm\/dd\/yyyy\#"))
What is wrong now?
Jan

"Barry Gilbert" wrote:

If you plan to use Doug's suggestion, you left off the Format
function:

=DSum("[CLUs Earned]","Mandatory Continued Learning Units","[SSN] =
'" &
Me.txtSSN & "' AND [Completion Date] #" &
Format(DateAdd("yyyy",-2,Date()),
"\#mm\/dd\/yyyy\#"))

Barry

"Jan" wrote:

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.