EmployeeID

[smallWorld]

Dear all,

In my table, I have a field called employeeAutoNo that has Auto Number data type. E.g: 5.

I have another field called EmployeeID. E.g.: ABC/5/2008, where 2008 is the year & ABC is a contant.

How do I get the get the value from employeeAutoNo and concat with the constant and year (year is taken from System Date) and then store into the EmployeeID field?

Thanks!

 

[DCrake]

Simple Software Solutions

Hi

This depends on how you are adding new employees..

if you are using an append query then in your column which populates the employeeID you will need ID:"ABC/" & x & "/" & Year(Date())

Where x is the AutoNumber.

If you are doing it in code then first identify the max auto number in the table and add 1 to it, and store it to a variable.

Then in your code use the same concat as above to write it to the field.

rs(fieldname) = "ABC/" & x & "/" & Year(Date())

 

[smallWorld]

Hi DCrake,

Thanks for the reply. Yes, that's what I'm precisely what I'm looking for.


MaxAutoVal = Max("EmployeeAutoNo", "EmployeeTable")+1

rs(EmployeeID) = "ABC/" & MaxAutoVal & "/" & DatePart("yyyy",Now())

I wanted the max auto number (EmployeeAutoNo) and do some string manipulation and then store them back into the EmployeeID field. Therefore, if there is a new employee and the maximum auto number is 5, the EmployeeID should store ABC/ 6 /2008.

My question is the codes ok? And where do place it?

Many thanks!