Random Numbers

[MTSPEER]

Hello,

I was wondering if you guys can help me write a query to generate random number in a field I have in my database. The field name is HICN, and the table name is Test_Table. I have records already in the table, hundreds of them. Also, the random number has to have 9 numbers and 1 letter at the end of the number.

Thank you!

[orange]

What have you tried? What result?

There is a sample here showing the generation of a random number.

[MTSPEER]

Haven't tried yet, I'm confused on how to get the letter at the end

[orange]

Pick a random number between 1 and 26, and select a letter from the alphabet (abc...xyz)

Here's a function that you could use.

'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author    : Jack
' Created   : 11/18/2010
' Purpose   : To Generate Random numbers between and including a range of numbers.
'Lo and Hi are the lowest and highest random numbers you wish to generate.
'The Randomize keyword is critical to getting different results for each Access session.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function randomNumber(Lo As Integer, Hi As Integer) As Integer
10       On Error GoTo random_Error
20    Randomize
30    randomNumber = Int((Hi - Lo + 1) * Rnd - Lo)

40       On Error GoTo 0
50       Exit Function

random_Error:

60        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure random of Module AccessMonster"
End Function

Three samples
?randomnumber(1,26)
1
7
16

[MTSPEER]

I don't want to have to pick a random number for each row, I just want to update the whole field with ranom numbers from clicking one query.

[orange]

Change the Integer assignments to Long in the RandomNumber function;
then try this

'---------------------------------------------------------------------------------------
' Procedure : TestAlphaRandom
' Author    : mellon
' Date      : 15/12/2014
' Purpose   :To pick a random number with a single, terminating random alpha
'---------------------------------------------------------------------------------------
'
Sub TestAlphaRandom()
    Dim a(26) As String
    Dim i As Integer
    Dim jNum As Double
    Dim klong As Long
   On Error GoTo TestAlphaRandom_Error
  klong = 999999999
    For i = 1 To 26
        a(i) = Chr(64 + i)
    Next i
    jlong = randomNumber(1, klong)
    Debug.Print jlong & a(randomNumber(1, 26))

   On Error GoTo 0
   Exit Sub

TestAlphaRandom_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TestAlphaRandom of Module AccessMonster"
End Sub

Here are a few samples


881858287Q
609171448T
381262479H
814044771M

[orange]

Did you try the routine in post#6 ?

[MTSPEER]

Sorry I am kinda new to this, can I put this into an update query?

[MTSPEER]

Also, I am having trouble thinking of a way to generate last names. They can all be Sara, but have them with numbers at the end. Like Sara1111, Sara1112 and so on. If you can help me out that would be awesome