Mid Text String's Number Bump by One

[CementCarver]

I have a text string 'G123-13' that needs to be incremented by one. It's stored as a text string and I can get the value through DLOOKUP, and can see the value if I use the following:

grow_lic = DLookup("[Licence]", "FULLGROWER")


licence_mid = Mid(grow_lic, 2, 3)

But how do you increase the licence_mid value by one and then put the 'G' and '-13' back onto the text string?

Any help would be appreciated.

CementCarver

[orange]

What is the business rationale behind this? One of the key concepts of relational database is 1 fact 1 field, and this seems to be trying to build some intelligence into a code/identifier.

When we usually think of incrementing something by 1 (that is adding 1 to something), the something is usually a number.
A best your "code" is some text concatenated with a number and the number part can be incremented.

There is a database at https://skydrive.live.com/?cid=cc4b0...Oz1XZPZFuCucdk that has a sample. I saw it referenced in another post earlier today --regarding custom autonumbers or something similar.

[HMEpartsmanager]

I have a text string 'G123-13' that needs to be incremented by one. It's stored as a text string and I can get the value through DLOOKUP, and can see the value if I use the following:

grow_lic = DLookup("[Licence]", "FULLGROWER")
licence_mid = Mid(grow_lic, 2, 3)

But how do you increase the licence_mid value by one and then put the 'G' and '-13' back onto the text string?

Any help would be appreciated.

CementCarver

Are you not almost there? Can something like:

grow_lic = DLookup("[Licence]", "FULLGROWER")
licence_mid = Mid(grow_lic, 2, 3)
NewLicense = "G" & Str(licence_mid + 1) & "-13"
Re-write the License back to the table?

Tim