Left function - don't ignore leading zeros!

[audrey]

In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have

caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)

This was working fantastic until I had to enter cases from 2009, ex: 0911000587

I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).

Is there anything I can do to force it to NOT ignore the leading zero?

Thanks in advance!

 

[jdraw]

Since you are dealing with "numbers" and leading 0s is critical, consider your numbers as text and use the format () function to help with leading zeros.

Access and other databases work best when dealing with atomic values ---1 concept 1 field. You can present data to the user or for reports by concatenating individual fields to make some meaningful text.

Concocting your own codes from a variety of fields can have unexpected issues -as you have found.

Most codes, numbers(phone number/part number/office number..) are really text which just happen to use digits in their representation. Use number data types for those things that participate in calculations/arithmetic.(or autonumber for primary key)

 

[audrey]

Thank you for your reply. I was afraid of this. Re-doing the relationships will be fun lol. I thought I was saving a headache by making it a numerical field, but I guess not. Thanks again for your response =D

 

[jdraw]

What exactly are you trying to do in plain English --no jargon?
Do you have a written requirement or specification?