Retrieving data from query to form

[JudyB]

I have a Query (Service Record) that provides the following information:

ServiceRecordID, EmployeeID, DeptName, JobTitleName, DateStart, DateEnd,
and WeeksService which is a calculated field that calculates the weeks
between DateStart and DateEnd.

I then have a form with a subform. The main form shows each employee's
personal information and the subform is used to input the information shown
in the Query above. I would like to have a text box on the main form that
shows the total number of weeks the employee has worked in the department he
is currently working in.

For example: an employee works in the Sales Dept for 52 weeks, moves to the
Purchasing Dept for 104 weeks and then returns to the Sales Dept where he is
currently working and has been there for 104 weeks. Total Weeks in the
current department (Sales) should show 156 weeks.

I know that if the DateEnd is left blank in a record that I should be able
to use that information to establish the employee's current department and
then use it in code to sum the weeks service calculated in my query. I am
just lost on how to accomplish that. I tried the code below in the text box
of the main form, but didn't have any luck:

=DSum("WeeksService", "Service Record Query", "EmployeeID=" & [EmployeeID] &
" and "DepartmentName=" & [DepartmentName])

Can anyone please tell me how to accomplish this task. Thanks in advance!

 

[Michael Gramelspacher]

I have a Query (Service Record) that provides the following information:

ServiceRecordID, EmployeeID, DeptName, JobTitleName, DateStart, DateEnd,
and WeeksService which is a calculated field that calculates the weeks
between DateStart and DateEnd.

I then have a form with a subform. The main form shows each employee's
personal information and the subform is used to input the information shown
in the Query above. I would like to have a text box on the main form that
shows the total number of weeks the employee has worked in the department he
is currently working in.

For example: an employee works in the Sales Dept for 52 weeks, moves to the
Purchasing Dept for 104 weeks and then returns to the Sales Dept where he is
currently working and has been there for 104 weeks. Total Weeks in the
current department (Sales) should show 156 weeks.

I know that if the DateEnd is left blank in a record that I should be able
to use that information to establish the employee's current department and
then use it in code to sum the weeks service calculated in my query. I am
just lost on how to accomplish that. I tried the code below in the text box
of the main form, but didn't have any luck:

=DSum("WeeksService", "Service Record Query", "EmployeeID=" & [EmployeeID] &
" and "DepartmentName=" & [DepartmentName])

Can anyone please tell me how to accomplish this task. Thanks in advance!

could you try:

=DLookup("DateDiff('ww',[startdate],NZ([enddate],date()))", "[Service Record
Query], "[EndDate] is null")

 

[Michael Gramelspacher]

could you try:

=DLookup("DateDiff('ww',[startdate],NZ([enddate],date()))", "[Service Record
Query], "[EndDate] is null")

try this instead:

=DLookUp("DateDiff('ww',[StartDate],NZ([EndDate],date()))","[Service Record
Query]","[EmployeeId] = " & Forms![Service Record Query]!EmployeeId & " and
[EndDate] is null")

This is based on the employee's current department having no end date.

 

[JudyB]

Hi Michael,

Thanks so much for responding. I tried your second suggestion and got the
#Name? error message. After looking closer, I realized that I had used a
public function in the WeeksService field of my query. So...I changed the
code to:


=DLookUp("WeeksService: CalcWeeks([DateStart],[DateEnd])","[Service Record
Query]","[EmployeeID] = " & [Forms]![Service Record Query]![EmployeeID] & "
and [DateEnd] is Null")

Still getting the #Name? error message. Can you identify where my problem
might be? Was I correct in making the change to reflect function in the
query field? Thanks again for your help.


--
JudyB

could you try:

=DLookup("DateDiff('ww',[startdate],NZ([enddate],date()))", "[Service Record
Query], "[EndDate] is null")

try this instead:

=DLookUp("DateDiff('ww',[StartDate],NZ([EndDate],date()))","[Service Record
Query]","[EmployeeId] = " & Forms![Service Record Query]!EmployeeId & " and
[EndDate] is null")

This is based on the employee's current department having no end date.