I have a form with a combo box for the user to select a code from one table which then populates another table. The tables are linked MS SQL. The fields are nvarchar. The codes are 01,02,03 etc. If I type the code into the table the leading 0 sticks, but if I select from the combo box the leading 0 gets dropped. I can't change the codes to type text, because MS SQL apparently doesn't allow text fields to be sorted. Any ideas on how to get the leading 0 to stick in the table?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
leading zero problem
- Thread starter mmaddox
- Start date
Try formatting your numbers to a two digit format. In the field for your 2 digit number that your combo box is based on, enter the following expression:
Format([YourField],"00"
That should switch them all to two digits, as well as get them to sort in proper numerical order, as opposed to the "access" method of storting (1,10,11,2,3,4,5,6,7,8...etc..)
Keep in mind that whatever you're basing your combo box on as criteria also needs to be in 2 digit format. Thus, you'll have to change everything that is using this combo box to the same custom format, or you'll get nothing returned on your queries.
Let me know how it turns out.
-Patrick
Nine times out of ten, the simplest solution is the best one.
Format([YourField],"00"
That should switch them all to two digits, as well as get them to sort in proper numerical order, as opposed to the "access" method of storting (1,10,11,2,3,4,5,6,7,8...etc..)
Keep in mind that whatever you're basing your combo box on as criteria also needs to be in 2 digit format. Thus, you'll have to change everything that is using this combo box to the same custom format, or you'll get nothing returned on your queries.
Let me know how it turns out.
-Patrick
Nine times out of ten, the simplest solution is the best one.
- Thread starter
- #3
I'm not sure if I used the Format as you described, but I was able to get it to work by using it in the Row Source box
SELECT Format([invitemcode.itemcode],"00" AS ic, INVITEMCODE.itemcodedesc FROM INVITEMCODE ORDER BY INVITEMCODE.itemcodedesc;
I don't understand why the zeros were dropped in the first place, but this works so I'm sticking to it.
Thanx
SELECT Format([invitemcode.itemcode],"00"
AS ic, INVITEMCODE.itemcodedesc FROM INVITEMCODE ORDER BY INVITEMCODE.itemcodedesc; I don't understand why the zeros were dropped in the first place, but this works so I'm sticking to it.
Thanx
Yeah..what you did looks fine...as long as it works!
Access won't hold a leading zero if the field is declared as a number type, just like a calculator drops it. If you're ever going to enter numbers that are really text (that you don't have to do math calculations on), it's a whole lot easier to set up the field as text...you'll have far less problems down the road. (That was something I didn't catch on to for a loooonnngggg time.)
Glad I could help, and good luck!
-Patrick
Nine times out of ten, the simplest solution is the best one.
Access won't hold a leading zero if the field is declared as a number type, just like a calculator drops it. If you're ever going to enter numbers that are really text (that you don't have to do math calculations on), it's a whole lot easier to set up the field as text...you'll have far less problems down the road. (That was something I didn't catch on to for a loooonnngggg time.)
Glad I could help, and good luck!
-Patrick
Nine times out of ten, the simplest solution is the best one.
- Status
Similar threads
- Locked
- Question
- Locked
- Question