If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating Dates
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. |
#12
|
|||
|
|||
Calculating Dates
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. |
#13
|
|||
|
|||
Calculating Dates
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. |
#14
|
|||
|
|||
Calculating Dates
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. |
#15
|
|||
|
|||
Calculating Dates
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. |
#16
|
|||
|
|||
Calculating Dates
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. |
#17
|
|||
|
|||
Calculating Dates
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. |
#18
|
|||
|
|||
Calculating Dates
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. |
#19
|
|||
|
|||
Calculating Dates
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. |
#20
|
|||
|
|||
Calculating Dates
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating time between two dates | J. Gutierrez | General Discussion | 2 | October 21st, 2005 05:53 PM |
Calculating differences in dates | Paul Sheppard | General Discussion | 5 | June 30th, 2005 01:18 PM |
Calculating Dates | Eric | Setting Up & Running Reports | 9 | September 17th, 2004 02:10 PM |
Calculating Weekdays between two dates | Marc | Using Forms | 3 | August 25th, 2004 07:36 PM |
Calculating Dates | Kerrick Sawyers | Worksheet Functions | 3 | October 7th, 2003 08:09 PM |