Convert a complete string into a number

[Werner]

Hi Forum,


I need a code number (any number) to identify or group different entity types from the same column
The Val([Entity]) function doesn't work in this case. Is there any function to convert a complete string into a number?

See example below:

Entity	Code
A-0-1	1
0-1-1	2
A-0-1	1
A-0-1	1
P-32-24	3
A84-R-W5	4
A84-R-W5	4
0-1-1	2
A84-R-W6	5

Any help will be much appreciated

[pbaldy]

Could be, but I don't see any pattern as to why a given entity gets a certain code. What rules are you applying?

[Werner]

Thanks for reply,
There's no rules. I just want to assign a number (any number), the same number to the same entity in column entity
Suppose we have a column with repeated names, then the code column should have 1 for all John, 2 for all Mikes and so on. Could be any number but the same number for each entity named the same.
Your comments or questions are welcome
Thank you

[pbaldy]

I'd have a table of entities, one record for each. You can use an autonumber field to get a unique code for each, or input your own value. Then in a query join the 2 tables together.

[Werner]

Sound excellent. I will give it a try
Thanks a lot for your idea

[pbaldy]

No problem, post back if you get stuck. Welcome to the site by the way!

[androo235]

Surely this one is solved - that is the answer isn't it?