Leading Zero Problem

  • Thread starter Thread starter Chuck
  • Start date Start date
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.
 
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
 
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...
 
Back
Top