World of expressions!

[Rodnee]

Hello there,

Sorry if this question has been answered before but I could not find a suitable reply in the search.

I wish to designate unique customer codes in my database during data entry.

For example;
Liz Stimp = LS1
Liz Stamp = LS2
Lyne Small = LS3
Lipo Suction = LS4

I have successfully linked the first and surname using a "Left 1st letter" in the expression but I do not understand how to add a 'unique' number to the code to give me the result I am after.

Expression so far is .....Left([First Name],1)+Left([Surname],1)

I do hope someone can help me as these customer codes have been the basis of my business so far and any correction would mean relabelling the whole shop!!!

Thank you for any replies

Regards Rodnee

 

[jdraw]

DON'T do that.
Investigate Autonumbers and Primary Keys and Normalization

It appears that you may have other issues in your data base design.

 

[Pat Hartman]

Names formed this way are useful for people but shouldn't be used as PKs. Autonumbers would be better. You could have a CustomerID which is an autonumber and is used in all relationships and a CusdomerCD which would be a code used for quick identification of a customer and used only for searching.

You can't just use DMax() against the table to find the maximum value because the numbers are left justified. So LS3 would be greater than LS10 which is not correct by human thinking but is by computer thinking.

I would create a query that separates the two parts of the code and then use DMax() on that query.

Select Left(CustomerCD,2) as CDLetters, CInt(Mid(CustomerCD,3)) as CDnum
From YourTable;

NextNum = Nz(DMax("CDNum","qSeparateCode","CDLetters = '" & Me.Letters & "'"),0) + 1
Me.NewCustomerCD = Me.Letters & NextNum

 

[Rodnee]

Thanks for the replies guys,

I may not have got my reasons across clearly for using this 'customer code generator'!

I will not be basing the database around this code, I simply need access to generate this unique code when members of staff are filling out the customer application form. The code will be generated whilst inputting information into the form. It should stop staff from accidentley reproducing customer codes (this has happened in the past!)

Thanks for any further help

 

[jdraw]

Research autonumber - that's what you need for PK unique record identifier maintained by Access.

 

[Rodnee]

Thank you Pat,

We have been in business for quite a while and our pricing of all our stock is related to these existing codes LS1, LS2 etc etc.

As you could imagine, relabelling thousands of items of stock is simply not a solution! I will not be using this code in Access as a PK as I understand the problems that can arise, It will simply be used when our staff are inputting new customers into the shop.

I want access to simply generate this code automatically when the member of staff inputs new customer details so they do not duplicate codes for the new customers.

PK's will be the auto numbers for the way the database works normally and the customer codes will only be used to check which items customers have in stock during a query relating to that item as it sells.

Is there any way you can use the first part of the code I have generated so far and then add the Dmax bit to that?

Thanks for your help

Damion

 

[G37Sam]

If you insist:

maxNumber = DLookup("max(mid(codes,3))", "table3") + 1

 

[Rodnee]

Thanks for the info, could you explain how it works and does what it does? cheers

 

[G37Sam]

Mid function => lets you choose part of a string
Max function => get's the maximum value of a certain field in a table
Dlookup function => Looks up values from a table

So you're basically looking up the maximum of a part of [codes] from [table3]

How can search MSDN for each of those functions to get a clearer image as to how they work exactly.

 

[Rodnee]

Thank you,

Very clear,

Regards Damion