creating an automatic stock number from another field

[eharris555]

I'm working on a database to use for my business. I have all of the tables created, but I need to create a stock number based on a description field using only a few characters from the description. Can anyone help me?

 

[Mile-O]

Can anyone help me?

It would be easier to if you gave more information such as what fields you have and how the code is built from the description field. etc.

 

[a.sinatra]

This will most likey be done in a query, but post you will need to post your table information.
________
Elysion

 

[Mile-O]

This will most likey be done in a query, but post you will need to post your table information.

Echo, echo, echo....anyway, it would most likely be done on a form.

 

[neileg]

Please, please, please do a search in here for posts on stock and inventory control. There is lots of good advice and a lot of examples of how not to do it!

 

[eharris555]

OK, here is my database...

Here are the tables I have. Candles, Color, Container, Customer, Embeds, Order, Packaging, Scent, Wax, Wick.
Starting at the top, Customer is linked to Order, Order is linked to Candles, and the rest of the tables are linked to Candles from there.
I use the Candles table to tell me what "ingredients" the particular candle needs.
In the Candles table, I have a Stock_no, Candle_name, and the rest of the stuff. I need the Stock_no to have the first part of the Candle name in it. That's the part I'm not sure how to do, or if there is a better way to do that, other than having to figure it out on my own...
Does that help any? BTW, thanks for the replies...

 

[neileg]

Depends what you want. You can use Left() to take the beginning of the name and add a number.

You could use an autonumber, but it all depends on what you expect from your number sequence.

If you are happy to have, say:
WHI0001
BLU0002
GRN0003, etc
then you can set up an autonumber field and concatenate the beginning of your Candle name with this autonumber.

If you want:
WHI0001
WHI0002
BLU0001
GRN0001
then it becomes a little more complex.