Leading Zero Problem

C

Chuck

I have a field within a table as Long Integer. It has to contain a 7 digit
field, first 2 digits is the fiscal year. The remaining 5 digits is a
sequence number. I have the input mask and format in the table set to
0000000. This provides the user the look of a 2 digit year so they see, for
example, 0500123. When the user selects to add a new record, I do a DMAX to
get the highest number in this field, and add one, so the next number, for
example would be 0500124. Works great, until I have to check for a fiscal
year change, say November 1, 2005, and the number has to change to 0600001.
I can extract the year from the system date, and I tried to extract the
first two digits of the field using Left. However, it drops the zero and I
get a year of 50 instead. I've tried changing the field to text and still
can't get it. Text seems to drop the zero as well. I need to keep it as a
field where I can add numbers to it, and then change it when the fiscal year
rolls around, then start incrementing it again. Any ideas? Thanks.
 
L

Larry Daugherty

Hi Chuck,

Your life might be simpler if you maintained two separate fields:
FiscalYear and YearSeq. Concatenate them anytime you need to show them that
way. FYSequence= format([FiscalYear], "YY") & format("00000",
Dmax([YearSeq] +1) Which ever way you do it, the Format([FiscalYear], "YY")
forces the two digit year.

HTH
 
C

Chuck

Thanks for the idea, I'll give it a shot.

Larry Daugherty said:
Hi Chuck,

Your life might be simpler if you maintained two separate fields:
FiscalYear and YearSeq. Concatenate them anytime you need to show them
that
way. FYSequence= format([FiscalYear], "YY") & format("00000",
Dmax([YearSeq] +1) Which ever way you do it, the Format([FiscalYear],
"YY")
forces the two digit year.

HTH
--
-Larry-
--

Chuck said:
I have a field within a table as Long Integer. It has to contain a 7 digit
field, first 2 digits is the fiscal year. The remaining 5 digits is a
sequence number. I have the input mask and format in the table set to
0000000. This provides the user the look of a 2 digit year so they see, for
example, 0500123. When the user selects to add a new record, I do a DMAX to
get the highest number in this field, and add one, so the next number,
for
example would be 0500124. Works great, until I have to check for a
fiscal
year change, say November 1, 2005, and the number has to change to 0600001.
I can extract the year from the system date, and I tried to extract the
first two digits of the field using Left. However, it drops the zero and I
get a year of 50 instead. I've tried changing the field to text and
still
can't get it. Text seems to drop the zero as well. I need to keep it as a
field where I can add numbers to it, and then change it when the fiscal year
rolls around, then start incrementing it again. Any ideas? Thanks.
Click to expand...
Click to expand...
 
Top