By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 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
  1.  
  2. strSQL = "SELECT TOP 1 EmployeeNo FROM tblEmployees ORDER BY tblEmployees.EmployeeNo DESC;"
  3.  
  4. Set rs = CurrentDb.OpenRecordset(strSQL)
  5.  
  6. Dim NewEmpNo As Long
  7.  
  8. NewEmpNo = rs.Fields("EmployeeNo")
  9.  
  10. Me.txtEmpNo = NewEmpNo + 10
  11.  
  12.  
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

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

Share this Question
Share on Google+
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
  1. 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

Post your reply

Sign in to post your reply or Sign up for a free account.