434,960 Members | 2,459 Online
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Numbers in Access

 P: 30 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. Expand|Select|Wrap|Line Numbers   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. Mar 15 '12 #1

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:
Expand|Select|Wrap|Line Numbers
1. Me.txtEmpNo = Clng(NewEmpNo) + 10

4 Replies

 100+ P: 124 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: Expand|Select|Wrap|Line Numbers Me.txtEmpNo = Clng(NewEmpNo) + 10 Mar 15 '12 #2

 P: 30 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? Mar 15 '12 #3

 100+ P: 124 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. Mar 15 '12 #4

 P: 30 Actually, I made the change to Long and it WORKS! Thanks!! Mar 15 '12 #5