Combining an autonumber field and a text field to show in a third (1 Viewer)

[hardhitter06]

Hi All,

Access 2003.

I have an online web shop where I need to create a database to store my items.

I need to generate item numbers combining my autonumber field and my category abbreviation field.

For example, the second item to be added into my database is a computer. I select computer and my autonumber generates a "2" since its the 2nd record/item being added to the database.

When I select computer, I have a category abbreviation field and CPU floods in.

Simply, I'm trying to combine this abbreviation with the autonumber, so in this instance, i would want cpu2 generated.

So my question is, how do i concatenate(combine the text and number together) to show in this 3rd field?? I'm assuming I need code in this third field to pull the values of the first two, but the way i have it, it's trying to add the fields and not combine the text and number...

Please help

 

[hardhitter06]

Well, I looked around, I am close with this

My new generated item number field has this as the control source:

=[Category] & [itemnumber]

The thing is I have the Category combo field showing the first column of the table and then next to it I have Category combo field showing the 2nd column of the table (or the abbreviation column). This is the column i want to concatenate with the autonumber. How would I do so??

 

[boblarson]

1. You are misusing Autonumbers. They are not intended for something like this and will only frustrate your attempts to use them. They are ONLY guaranteed to give you a UNIQUE number, not sequential or even positive numbers. Just because it appears to you that they do doesn't mean that they always will.

2. An Autonumber is for the database to maintain referential integrity. As the old saying goes, "An Autonumber is not for human consumption."

3. I'm not clear as to why you need to actually create this identifier. So you want to identify an item with a unique identifier, correct? So, you can have the autonumber create the unique number (although there may be times when it is -2390232 or something like that) and then you could have your type. But you don't store the concatenated two together. You just use a query when you need to display them together and not store it in the table (look up normalization).

 

[boblarson]

Oh, and actually you can use the DMax+1 for your number. And I just noticed that you have been around the forum long enough you should know to not store the concatenated value (I see you didn't explicitly say you were but I wanted to make sure that was clear).

 

[hardhitter06]

Here's the thing, I need to know what the item number is/will be (with the category abbreviation and unique number) at the input screen based on my duties regarding this information.

Since creating databases..and i am no expert, but all of my tables use autonumbers and ive never had the autonumber not go in sequential order. I have never seen a negative either and all that really matters is that the autonumber is never the same and it counts for me.

I dont know what Dmax+1 is or how to apply it..

 

[stopher]

Since creating databases..and i am no expert, but all of my tables use autonumbers and ive never had the autonumber not go in sequential order. I have never seen a negative either and all that really matters is that the autonumber is never the same and it counts for me.

I can only agree with what Bob has already said. Maybe another point will convince you:

Suppose you accidentally delete a record. If you use the autonumber as something meaningful like you suggest then you will need to reinstate that record with exactly the same number. But autonumber won't let you - it will just give you the next number.

Bob's proposal of using DMax is a well used method. Take a look here for some help or search for Dmax and autonumber.
http://www.599cd.com/tips/access/incrementing-your-own-counter/

hth
Chris

 

[hardhitter06]

Thanks Chris,

You example was great and it makes more sense.

Thank you too Bob.

Works great!