Convert String Values to numbers (1 Viewer)

Lightwave

Ad astra
Local time
Today, 08:30
Joined
Sep 27, 2004
Messages
1,521
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

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
11,613
What do you suggest the 10th through 26th letters of the alphabet get converted to?

AB = 12
L = 12

BF = 26
Z = 26
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Jan 23, 2006
Messages
15,364
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

Ad astra
Local time
Today, 08:30
Joined
Sep 27, 2004
Messages
1,521
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.
 
Last edited:

Lightwave

Ad astra
Local time
Today, 08:30
Joined
Sep 27, 2004
Messages
1,521
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

Immoderate Moderator
Staff member
Local time
Today, 03:30
Joined
Feb 28, 2001
Messages
27,001
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,

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

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

Code:
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).
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Jan 23, 2006
Messages
15,364
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

Super Moderator
Staff member
Local time
Today, 08:30
Joined
Feb 19, 2013
Messages
16,553
I agree with Doc. Be aware the number type is long and can be negative
 

Users who are viewing this thread

Top Bottom