Help - Changing the 1st digit of a 9-digit text field automatically and randomly

[russi]

Hi.
Bottom-line is I have a database with a SSN field of 9-length and stored as text (based on business requirement).
I have thousands of records.
I need to replace the 1st or last number in the field with a randomly selected letter or number in such a way as to also not allow for duplicates of what is created.

Ideas???

Russ

 

[dosgimp]

1 letter random no duplicates.. will only yeild. 26 possibilites..

 

[The_Doc_Man]

This is a two-part problem.

FIRST, SSN is supposed to be unique already. So if you have duplicates, it is because your table structure is not normalized. If your tables were normalized, you wouldn't have to play games with what SHOULD be a unique key field.

SECOND, you need to look at the RAND function (RND?) to generate a random REAL FRACTION ('cause that's what VBA's random generator generates), then scale it by multiplication & addition to something in the range between 33 and 95 (or 33-126 if lower case is OK). Then convert it to an integer, generate CHR$(x) of the number, and stuff that in the position you want. Look at the MID$ function and you will see that it is unique among the string functions. You CAN use it on the left side of an equation, unlike its cohorts in crime, RIGHT$ and LEFT$.

If you cannot accept punctuation marks, then your function has to generate the character but then screen it and perhaps go back and try again if the character you get is wrong. By the way, you might also have to screen out the case where what you generated matched what was there in the first place.

 

[russi]

First of all thanks to both of you for replying.

I must not have been clear, because each SSN is unique.
And while I know that there are only 26 letters n the alphabet, because the other numbers are essentially unique, this should not matter.

The purpose is to create a file with the ssn included, but 'encrypted' so to speak, so that it is not truly recognizable.

Actually as I think about it, could I just replace the 1st and last number with the same letter?

HOW WOULD I GO ABOUT DOING THIS?

Russ

 

[KKilfoil]

Assuming your table has an autonumber id field:

In a query, make a calculated field as follows:
RandomLetter: = Chr(Int(Rnd([Your_ID])*26)+64)

The Rnd function generates a pseudo-random number from 0.000 to 1.000 using your unique ID as the seed (making the value reproducable, by the way). If you want to increase the 'security' on this, add some constant arbitrary offset value, like Rnd([Your_ID]+1234).

The Int(yadda*26) part gives you an integer value from 1 to 26 for your random number.

The Chr(yadda yadda +64) part returns an ACSII value from 'A' to 'Z' (as A is value 65).

Now you can use this letter how you see fit. For example, to replace the 3rd character, use:
MySSN: = left([ssn],2) & [RandomLetter] & mid([ssn],4,6).

This will NOT necessarily generate a unique code, as conceivably
123456789 could become 12A456789, and
129456789 could become 12A456789,
but this is quite unlikely, because you would first have to have the other 8 characters be the same, and then have a 1/26 change of substituting the same letter.

Lastly, you need to have an update query store this value. You could even have the 'offset value' be a run-time parameter in the query, so that only you could know what value was used.

HTH

 

[russi]

Thank you, so much!

Russ