Continuation of an unsolved issue

[JudyB]

I currently have a database that adds all service time an employee
works in each job classification. I need to modify the database to also
include any service time an employee may work in a job title that is above
his/her current job title in the line of progression (LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.04) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.05) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks?

I was given the following code, but have been unsuccessful in getting it
to work:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

LOP Coding (1.1.04) - The first digit represents the Department, the second
digit represents the area, and the last two digits represent the job.
Therefore, the first and second digits of the LOP will need to be equal and
the last two digits must be equal or greater.

When I type the code in the Control Source field and then clicked out
of the field, the system automatically changes the text back to the original
code.

This is my first database, so I am very green. Can anyone provide
additional help? Thank you so much!

 

[Beetle]

I have a few questions for starters;

1) In the previous thread you indicated that an LOP of 1.1.04 would be
higher than 1.1.05. Now you seem to be indicating that 1.1.05 is
the higher LOP. That's an important distinction that should be clarified.

2) Do you have a field named Current_LOP? If not, what is the name of
the field that stores the employees current job position, as opposed
to their former job position?

3) What do you mean when you say the system is automatically reverting
back to the original code? What is the "original code"?

 

[JudyB]

Hello Beetle,

Thanks for quick response.

Answers to questions:

1) Since posting of initial thread, I decided to make the higher number
(1.1.05) the higher LOP to avoid having to create an additional field.

2) I do not have a field name Current_LOP. I have a subform with fields -
LOP, Department_Name, Job_Title_Name, Date_In, Date_Out, and Time_Served
(which automatically calculates the weeks served). If the Date_Out is Null,
the LOP, Department, and Job Title in that row are set as "Current".

3) The original code was: =DSum("[WeeksService]","Service Record
Query","[EmployeeID] = " & [EmployeeID] & " And [JobTitleName] =
Current_Job_Title_Name")

Hope I understood questions and gave appropriate answers. Thanks again.


--
JudyB

I have a few questions for starters;

1) In the previous thread you indicated that an LOP of 1.1.04 would be
higher than 1.1.05. Now you seem to be indicating that 1.1.05 is
the higher LOP. That's an important distinction that should be clarified.

2) Do you have a field named Current_LOP? If not, what is the name of
the field that stores the employees current job position, as opposed
to their former job position?

3) What do you mean when you say the system is automatically reverting
back to the original code? What is the "original code"?
--
_________

Sean Bailey

I currently have a database that adds all service time an employee
works in each job classification. I need to modify the database to also
include any service time an employee may work in a job title that is above
his/her current job title in the line of progression (LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.04) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.05) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks?

I was given the following code, but have been unsuccessful in getting it
to work:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

LOP Coding (1.1.04) - The first digit represents the Department, the second
digit represents the area, and the last two digits represent the job.
Therefore, the first and second digits of the LOP will need to be equal and
the last two digits must be equal or greater.

When I type the code in the Control Source field and then clicked out
of the field, the system automatically changes the text back to the original
code.

This is my first database, so I am very green. Can anyone provide
additional help? Thank you so much!

 

[Beetle]

This should work;

=DSum("WeeksService","Service Record Query","EmployeeID= " & [EmployeeID] &
" And Left(LOP,3)= """ & Left([LOP],3) & """ And Right(LOP,2) >= """ &
Right([LOP],2) & """")
--
_________

Sean Bailey

Hello Beetle,

Thanks for quick response.

Answers to questions:

1) Since posting of initial thread, I decided to make the higher number
(1.1.05) the higher LOP to avoid having to create an additional field.

2) I do not have a field name Current_LOP. I have a subform with fields -
LOP, Department_Name, Job_Title_Name, Date_In, Date_Out, and Time_Served
(which automatically calculates the weeks served). If the Date_Out is Null,
the LOP, Department, and Job Title in that row are set as "Current".

3) The original code was: =DSum("[WeeksService]","Service Record
Query","[EmployeeID] = " & [EmployeeID] & " And [JobTitleName] =
Current_Job_Title_Name")

Hope I understood questions and gave appropriate answers. Thanks again.


--
JudyB

I have a few questions for starters;

1) In the previous thread you indicated that an LOP of 1.1.04 would be
higher than 1.1.05. Now you seem to be indicating that 1.1.05 is
the higher LOP. That's an important distinction that should be clarified.

2) Do you have a field named Current_LOP? If not, what is the name of
the field that stores the employees current job position, as opposed
to their former job position?

3) What do you mean when you say the system is automatically reverting
back to the original code? What is the "original code"?
--
_________

Sean Bailey

I currently have a database that adds all service time an employee
works in each job classification. I need to modify the database to also
include any service time an employee may work in a job title that is above
his/her current job title in the line of progression (LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.04) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.05) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks?

I was given the following code, but have been unsuccessful in getting it
to work:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

LOP Coding (1.1.04) - The first digit represents the Department, the second
digit represents the area, and the last two digits represent the job.
Therefore, the first and second digits of the LOP will need to be equal and
the last two digits must be equal or greater.

When I type the code in the Control Source field and then clicked out
of the field, the system automatically changes the text back to the original
code.

This is my first database, so I am very green. Can anyone provide
additional help? Thank you so much!