Form Help - Bringing data together

[Oucch]

I have a basic form where the customer fills in; First Name, Surname, Address, Postcode and Telephone. I need to create a unique "customer id" for each person. So once they have filled in the required spaces, i run an append query to the customers table. The "customer id" must consist of the first three letters of their surname and 4 unique numbers.

To get the letters (this works) I have:
=Left$([Surname],3)

But, im having problem adding the numbers onto the end of the letters;
& (Max([=Right([number],4)])+1)

[number] is a hidden textbox on the form where the customer enters the data - 1234.

Am i doing this right? or is there anything I need to do differently? thanks.

 

[John Big Booty]

Does (Max([=Right([number],4)])+1) return the expected result when used in isolation?

 

[RuralGuy]

What is wrong with using an AutoNumber rather than your "smart" number?

 

[Oucch]

Does (Max([=Right([number],4)])+1) return the expected result when used in isolation?

I tried entering it on the form and hiding, this returns: #Name?
I also tried adding it into the append query but had no luck

 

[Oucch]

What is wrong with using an AutoNumber rather than your "smart" number?

How can I set the autonumber to 4 digits?
Possibly to start at 0001....this would be much easier.

 

[CraigDolphin]

I agree with RG. An autonumber is what you should store in the table as your CustomerID.

Any time you need a 'special' id code, you can always calculate it on the fly using something like
=Left([Surname],3) & Format([CustomerID],"0000")

 

[Oucch]

I agree with RG. An autonumber is what you should store in the table as your CustomerID.

Any time you need a 'special' id code, you can always calculate it on the fly using something like
=Left([Surname],3) & Format([CustomerID],"0000")

Umm...im not that experienced at access, could you explain how i go about this? thanks.

 

[RuralGuy]

Craig is of course correct but I was actually advocating for not using the AlphaNumeric scheme at all but simply using an AutoNumber for the CustomerID. Is there some reason this ID needs to be visible?

 

[CraigDolphin]

You would put the example I gave as the control source for an unbound testbox on a form which is bound to your customer table. Whenever you change records, the textbox would show you the alpha numeric form of the id. Or you could use the same logic in a calculated field of a query which uses the customer tsable as its source.

The point is, that you can always calculate this kind of field anytime you like, because the calculation is predictable, and can be based on an automatically incrementing number which you don't need to do anything to update when you create new records in the customer table.

And I should have said before that I agree with RG that the whole alpha-numeric system is a rather pointless exercise. A simple autonumber does everything you need without all the palaver. But regardless of whether you actually need to use such a system, you should not waste file space storing something like this when it can be calculated at any time.

Let me ask you a question to demonstrate what this type of thing sets you up for: What do you do with your alpha-numeric key if a customer changes their surname? (Marriage/Divorce/Vanity etc).

How do you keep it up to date once you learn of a name change? Are you going to go through every table in your db and update them?

With an autonumber you only ever have to update the name itself. There are no outdated foreign key values to worry about in other tables.