Numbers in Access

[Wesley Hader]

I have a table that holds Employee Information which includes the following fields: EmployeeID(AutoNumber), EmployeeNo(Long Integer), EmployeePin(Long Integer), and EmployeeName(Text). I assign employee numbers in increments of 10. (i.e. 10, 20, 30, 40...)

I use the following code in a form's on load event to to assign a unique employee number to the EmployeeNo Field.

 
strSQL = "SELECT TOP 1 EmployeeNo FROM tblEmployees ORDER BY tblEmployees.EmployeeNo DESC;"
 
Set rs = CurrentDb.OpenRecordset(strSQL)
 
Dim NewEmpNo As Long
 
NewEmpNo = rs.Fields("EmployeeNo")
 
Me.txtEmpNo = NewEmpNo + 10
 
 

The problem is that once I have an employee number of 90 my code never produces a number higher than 100. I know what's happening and how Access sees numbers in the following order 10, 100, 20, 30, 40....80, 90.

My question how can i get my access query or SQL statement to see 100 as the highest number instead of 90.

[BikeToWork]

You say your Employee Number field is a long but the sorting you describe is for a text field. Numbers are sorted numerically in Access. Check again on the datatype of the Employee Number field.

Try:

Me.txtEmpNo = Clng(NewEmpNo) + 10

[Wesley Hader]

Thanks for the quick reply!

You are correct. EmployeeNo is stored as a text field. However the example you provided did not change the outcome. I feel like a number field should be stored as such, so I will change that. Are you saying that a number stored as Long will sequence the way I need?

[BikeToWork]

Change the datatype of your EmployeeNo field to long and the field will sort numerically. Previously, I did not notice in your code that NewEmpNo is already declared as a long, so the Clng function will not do anything there.

[Wesley Hader]

Actually, I made the change to Long and it WORKS! Thanks!!