Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Updating MS Access table data with Random Values

    Hi All,

    I want to update MS Access table data with Random values.
    So please suggest me fastest way of doing it since table may contains thousands of records.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use an update statement
    eg
    Code:
    UPDATE MyTable SET MyColumn = rnd() * SomeScalingFactor
    items in italics are the name of the table and column you want to update
    item underlined is a scaling factor that multiplies the random number. RND on its own returns a decimal value (probably double precision) with a range of 0 to 1.


    the help file usually includes an example to get an integer range
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    5
    But some columns will be varchar, some columns will be integer. So how to take care of it.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If they are integer then as suggested before use the example supplied in the helpfile to generate a random value in your desired range
    if its varchar then you could generate random values using repeated calls to RND in and then translate the returned value using the chr function. you'd probably have to write a function to do that though.
    ASCII symbol table
    eg:-

    Code:
    public function GenRandCharValue(Optional NoChars as Integer = 8) as string
    GenRandCharValue = "" 'set up our default value
    if NoChars<=0 or >= 250 then 'there having a larf, reset it to 8 and ignore the little feckers
      NoChars = 8
    endif
    
    'what characters to use
    'as its sample code I don't care lets limit ourselves to A to Z ONLY
    'thats ASCII 65 to 90
    
    Dim iLoop as integer          'the loop counter
    Dim NextSymbol as integer 'holds the value of the random number in ther specified range
    For iLoop = 1 to Nochars  'generate a symbol each pass through the loop
      'NOTE we start at 1 for the loop to make certain we add NoChars symbols to the return value
      NextSymbol =Int ((90 - 65 + 1) * Rnd + 65) 'generate a psuedo random value in range 65..90 which represents the ASCII values of A..Z
      GenRandCharValue = GenRandCharValue & chr(NextSymbol) 'append the CHR representation of that symbol to the return value
    next iLoop 'keep going till we have created enough symbols
    end function
    Last edited by healdem; 12-26-12 at 11:23.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •