How do i increment part of of a numeric string? Part 1

[shellj]

My database stores stock details. Once records are added to the Stock table using a form, a unique number is generated using the information input. For example, items added from the Designer Fornarina have a code 18, if the item is a skirt the code is 12, if the colour is black the code is 01 and if the size is Small the code is 01. The number should look as follows: 181299990101. The problem I am having concerns the '9999' part of the code. If I have two completely different skirts (ie design) but the colour, designer and size is the same, I need to increment the '9999' by one so that I have unique numbers for each item of stock.

Can anyone advise me of the best way to achieve this?

Thanks in advance.

 

[KenReay]

Hi

If you increment 9999 it will overflow to 10000, or is 9999 just the place holder?

Any way to your question you need to look at Left(), Right(), mid(), Format(), VAL() in help, something like:

incString = Left(OrigString,4) & Format(Val(Mid(Origstr,5,4))+1,"0000&quot & Right(OrigStr,4)

there are actually several way to do this, but this should get you on track

181299990101

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[shellj]

Thanks Ken, I'll give that a try.

 

[shellj]

Yep, that worked!

Thanks again.

 

[JeremyNYC]

Yipes. You're storing a whole bunch of differnet pieces of information in a single field. That's a really bad idea, and it will require you to jump through many such hoops. If you store each of those bits of data in a separate field, you can then concatenate them in queries or a function, using the format command, to put them all together the way you want. That way you'll be able to manipulate each bit much more easily, and you'll be able to have lookup tables that link to each part, revealing more information.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.