Results 1 to 4 of 4

020911, 12:55 #1Registered User
 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.60055065155029E03
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.600551E03
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 "E03" which i think is general number but rather as ".002600551".
how would i format it to be like this.
thanks

020911, 14:08 #2Moderator
 Join Date
 Mar 2009
 Posts
 5,440
Provided Answers: 14Declare 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!

020911, 16:18 #3Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59if 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. E03 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

020911, 16:26 #4Registered User
 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