Convert String Values to numbers

[Lightwave]

OK this isn't quite as simple as the title states - I've tried CINT and Val functions don't quite get it right.

I'm currently doing a transform of data between systems and I have a table of of Tables... which relate to mapping.

I would like a number to uniquely identify each table which at present are uniquely identified by a text string.

Is there a function that will consistently convert a text string to a number eg

ABBA = 1221
CBD = 324

etc... so that if tables are in different tables they can be mapped to an equivalent index. The actual index doesn't matter if it is more or less digits so long as it is consistent.

I'm trying google but its not coming out for me.

 

[plog]

What do you suggest the 10th through 26th letters of the alphabet get converted to?

AB = 12
L = 12

BF = 26
Z = 26

 

[jdraw]

Is your algorithm such that the alphabetic position represents the digit??
ABCD = 1234
DCBA = 4321

Since digits go 0-9 what alpha translates to a 0?

You could start with 123456789 ABCDEFGHI

More details as applicable.
Good luck.

 

[Lightwave]

What do you suggest the 10th through 26th letters of the alphabet get converted to?

AB = 12
L = 12

BF = 26
Z = 26

Good point

Any unique character array needs to be a unique number - I guess start at 10 up to 36 and then concatenate those and then convert to number.

 

[Lightwave]

Is your algorithm such that the alphabetic position represents the digit??
ABCD = 1234
DCBA = 4321

Since digits go 0-9 what alpha translates to a 0?

You could start with 123456789 ABCDEFGHI

More details as applicable.
Good luck.

As per PLOG AB needs to be unique from BA

I think I might be making this more difficult than it is.

 

[The_Doc_Man]

Well, normally I wouldn't suggest this, but you've been around for a while and aren't a novice. Rather than trying to generate some Monte-Carlo number, you COULD look up a table's internal ID number by doing a query or DLookup. That would be a number that is simpler to find and will be absolutely unique within a database. For example,

TblID = DLookup( "[ID]", "MSysObjects", "[Name]='" & table-name-goes-here & "'" )

Or you could run a query against that table to make your list.

SELECT ID, Name FROM MSysObjects WHERE Type = 1 ;

Whatever you do, DO NOT TRY TO WRITE to MSysObjects. Your DB will go Bang-Zoom into the great dust nebula in the sky, probably unrecoverable or at least not completely recoverable. You can open Object Browser from a code window and look up AcObjectType to see what the various type codes represent. You can also look up AccessObject to see the properties of an object, which actually are fields in the MSysObjects table (though the table has more fields than are reported by the AccessObject definitions).

 

[Lightwave]

Thanks DOC

 

[jdraw]

I agree with Doc, given a little more info.
You want unique numbers for tables, and his query does that and uses m$oft's(Access) numbers.

 

[CJ_London]

I agree with Doc. Be aware the number type is long and can be negative