I have an emp no which can start with zero. i put the data type as number but the value zero is not taken at the beginning. now i have changed it to text. but the problem now is that i was using the dlookup command to get the rest of the data now since i changed it to text its showing a runtime error on the dlookup
In the table design view of your table go to the format section and Put x 0's (zeros) where x = the number of digits in your employee number. You will now get your leading zeros, when ever the number entered is less than the number of zeros you have in the format filed.
Just some additional info to add to the fine advice you have already received ...
.....
If you keep your empno as a TEXT field ... I assume you know the length you wish to have the value ... ie 6 characters (001234) or whatever. So ... when the data is input, it is important that you pad your data with leading zeros in order to create a consistency within your data. You can use the After Update of the empno control on the input form to accomplish this ...
Code:
Public Sub empno_AfterUpdate()
Me.empno = Right("000000" & me.empno,6)
End Sub
In addition, if keeping the field as TEXT, and your users search for a empno, you will have to pad the value they are search for with leading zeros too.
Basically ... ANY time your are using empno, you will want to ensure the value you are searching for is 6 characters (or the length you have chosen in your tabel design). An example for the dlookup would be ...
As you can imagine this concept of keeping it all the same length can be a tedious task ... so ... if your empno field is truly a numeric value and does not have any letters or separator characters (ie: like a phone number or government ID), then I would suggest that you keep it as a numeric/long integer datatype. Then when you wish to VIEW/DISPLAY the value, use the Format property of the control you are using to display it (as mentioned in a previous post) and set it according to the length you wish to show ... so for a "6 character" empno that is datatyped as Number/Long Integer, you could set the Format property to ...
000000
If you define the Format property at the table level, each time you create a bound control using a "Field List", the Format property will be inherited from the Table Design, however, with existing controls that are bound to that field, you will have to go in and set the format manually ...
you need to think carefully about this, although it is probably too late
if you have a "number" for an employee, then you should be aware that a number doesnt have leading zeroes. ie 023456 and 0023456 are both just integer 23456. Displaying leading zeroes is just a presentation issue, and you would not be able to store two different records with employy refs "023456" and "0023456"
if you have a string for the "employee number", then you can have "023456", and "0023456", but now you are storing strings rather than numbers. You could change numeric fields to strings, but depending on how you have it set up at the moment, this may affect relationships and code you have already developed. It is importnat to realise that string handling differs from numeric handling (eg sorting unequal length strings is different to sorting numbers)
The best way is to use an autonumber key on the employee table, and use THAT to manage relationships between tables. Now the "true" employee number is just another field/attribute in the employee table, and this could now be changed between number/string formats without affecting anything.
If you haven't got too far, you could presumably change to this sort of setup, but it may be too difficult now.
