Date Difference IF statement

[DAHMB]

I am trying to write a code that will:

• check an employees date of hire field
• compare it to todays date to get a years of service number
• then check the emoployees rank
• and if the rank equal 1 and the years of service is less then 1 then assign the value of 1
• then look up 1 in the table tblOvertimeRate sOf Pay and assign the vlaue listed in column 2 of that table to the rate of pay field in my form.

I know all this is vague but once I get it started I can zero in on the specifics

My form consists of the following fields with the following values:

EmployeeID - text
OTDate - date
HoursPay - double
RateOfPay - currency
TypeOfJob - Integer
Notes - text

The employee date of hire is contained in a tabel called tblEmployees in a field called DateOfHire.

Any help would be greatly appreciated.
Dan

[Delerna]

Hmmm, it is a bit vague, not sure exactly what help you want, so here goes.

I see 2 choices
1) I think you could obtain all of that in a query and base the form off the query
2) You could also achieve that in vb code on your form.

Which is the best option depends on lots of things and you are probably in the best position to make that choice.

Either way you will use the datediff() function with the Now() function and your employees hire date field as parameters to calculate the years of service. Then you would wrap that up in an if then else statement for code or an iif(codition,tr ue,false) function in a query to handle the employees rank.

In a query you would join to tblOvertimeRate s to get the value from column2. In code you would write a query and execute it to get the value for the current employee on the form using either DAO or ADO data objects.

Does that help to get you started?

[DAHMB]

Thank you, I would like to do it all in vb code. I would like to create a code that runs on exit of the [HoursPay] field.

In my form I have a field called [EmployeeId] that is pulls from a combo box based on a query called qryEmployees. I would like the code to check the qryEmployess for the EmployeeID DateOfHire field and get the date of hire then do a datediff with the field DateOfJob in my form and come up with a years of service number then check the EmployeeRank field in the query qryEmployees and get the value from there, then do something like:
If yearsofService (obtained from above code) is greater then 4 but less then 3 and EmployeeRank (also obtained from above code) = 5 then Rate of Pay = 4
else
and I can figure it out from here.

I know this is vague but I just need help in writing the sql code and all the proper formats and I could put it together from there.

Thank you for your time and help.
Dan