Increment Numeric portion of a string

[Barry]

I have a string in which i would like to increment portions of the string
automatically. The String is "LC06-0001-01"

What i need to do with this is first select the current year "LC06". i then
need to increment the second part of the string from 0001 to 9999 per year so
that if at the end of the year the string reads "LC06-5648" then in the new
year the first record will read "LC07-0001".

The last part of the string is got from a subform which can have values of
01 - 99 depending on the number of records entered. I need this to check the
max record and add 1 to the string.

I know this is probably childs play for some people but i am new to this and
i am having awfull trouble trying to figure it out.

Can someone please help me or point me in the right direction? Thanks in
advance

 

[Douglas J Steele]

"Smart keys" like that are not recommended. You're trying to store 3 or 4
different pieces of information in the same field, which is a violation of
database normalization rules. You really should have multiple fields
instead. If you need that key for backwards compatibility, you can always
create it by concatenating the individual fields.

If you have no choice, though, in what context are you having to increment?
Is it simply a case that you need to determine the maximum value currently
in the database and then increment it for the next record, or is there some
other requirement?

To determine what the highest value used for the current year is, you could
use something like:

Dim strCurrMax As String

strCurrMax = DMax("IDField", "MyTable", "IDField LIKE 'LC" &
Format(Date(), "yy") & "'*")

Once you've got the current max, you can use the Split function to break it
into its component parts:

Dim varParts As Variant

varParts = Split(strCurrMax, "-")

If strCurrMax is "LC06-0001-01", that will result in a 3 element array,
where varParts(0) will be LC06, varParts(1) will be 0001, and varParts(2)
will be 01

Do arithmetic on the appropriate elements of varParts, and reconstruct your
string.

 

[Barry]

I have not been very accurate in my post, this string is actually stored in 2
different fields, the first part of the string is used as the job number for
the job contract within the company I am working for and is stored as
"LC06-0001"
This number is basically incremented as a count of jobs that we have sourced
and will be the main id for all other modules within my program like pricing
and drawing logs etc...
(I.e. LC06-0526 would be the 526th job contract in the year 2006; we do not
expect this value to exceed 3000 at the moment)

The second part of the string is added when certain goods (Passenger Lifts)
are added to that contract so that if 12 lifts were added to this contract in
the subform it would give the numbers LC06-0526-01 to LC06-0526-12

Unfortunately the LC06-0001 part of the string has been used in the company
to allocate jobs since the beginning so I don’t think changing this is an
option but the second part of this string is not set in stone so if you have
any ideas that would work to my advantage please advise.

 

[Douglas J Steele]

Hopefully my first answer gave you enough information.

Note that modelling the table properly (with separate fields for all the
different fields) doesn't preclude you continuing to use the existing key
elsewhere.

Create a query that has a computed field that concatenates the various
fields into the heritage format, and use that query wherever you would
otherwise have used the table.