Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    12

    Unanswered: Simple VBA Module - Format the Random number generated

    simple for the average, however i am below
    i am trying to generate a random number that can be repeated time and time again so that when i select a sample (today) i can rerun a query and generate the same sample. Therefore, i use a seed number which i enter on my own. i have a list of employees with a unique Employee number. so every transaction line will have the same random number (for each employee)as i am using the Emp# to generate the random number.

    so i have done this as a function in the query. however, i use it often enough that i wanted to build a module. now i have got it to work, however i need a little assistance in formatting the number.

    using the following formula in the query.
    RandNo: Rnd(-[EmployeeID]*[Enter_Seed])
    i get the following result.
    RandNo
    2.60055065155029E-03

    however when i use the following as module
    Public Function RandNo(EmployeeID, Enter_Seed)
    RandNo = Rnd(-[EmployeeID] * [Enter_Seed])
    ' EmployeeID is a unique number from each record such as employee id
    'enter a seed number is required to repeat the same calc but i can vary it.

    End Function

    now here is the number i get

    Expr1
    2.600551E-03

    notice how there are only 6 digits behind the decimal.

    what i want to do is to have the same number as above and if not then at least 10 digits after the decimal.

    also probably along the same line of questioning. if i do not want the number expressed in the module as a "E-03" which i think is general number but rather as ".002600551".
    how would i format it to be like this.

    thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Declare the function with the proper type (probably Double in this case) and/or use the Format() function to display the number with the required number of decimal places.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want repeatability of a random number then store the seed value and re use that. if you don't specify a seed val,ue then the function uses some mangling of the current date and time.
    specifying the same seed value means you use the same starting off point each and every time

    bear in mind that what you see isn't neccesdarily what is stored in the computer world it depends on the format you use.

    the underlying data reamins the same but the way you present it can change dramatically

    the Exx notation is the so called scientifif notation that expresses a number to 6 decimal places multiplies by the mantissa the 2 digits after the E. E-03 meansas your number was actually 0.002600551, well at least rounded to 6digits.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2011
    Posts
    12
    thanks
    problem solved.
    now understand what 'double' vs 'integer' is vs 'scientific'
    also now understand a bit more logic of vba

    the seed, is just that, a number that i decide each time and enter it when i run a query which uses the module.

    here are 2 of essentially the same modules, but the formatting is applied on the 2nd one to only 2 decimal places.
    not the cleanest but they worked.

    Public Function RandNo(unique_number, Enter_Seed) As Double


    RandNo = Rnd(-[unique_number] * [Enter_Seed])

    ' unique number is a unique number from each record such as employee id
    'enter a seed number is required to repeat the same calc but i can vary it.

    End Function

    Public Function RandNo2_formatted_NO(unique_number, Enter_Seed) As Double



    randNo3 = Rnd(-[unique_number] * [Enter_Seed])

    RandNo2_formatted_NO = Format(randNo3, "##0.00000000")

    ' unique number is a unique number from each record such as employee id
    'enter a seed number is required to repeat the same calc but i can vary it.

    End Function

    thanks for your help

Posting Permissions

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