update query random number

K

Kenny G

Hello,

I need some help on generating a number with an update query. I have run
the query and the query runs and will generate a random number each time I
run the query. I'd like to have the numbers generated with only one click.
The below code is in a mod and the reference to the mod is in the query. I
was told I need some loop or do until code in this. Any help would be
appreciated.



Public Function GenPrimaryValue() As String
Dim strStart As String
Dim strDate As String
Dim strEnd As String
Dim CrntDate As Date
Dim intChar As Integer
Dim upperbound As Long
Dim lowerbound As Long
Dim X As Long
Randomize

CrntDate = Now
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format(DatePart("m", CrntDate), "00")
strDate = strDate & Format(DatePart("d", CrntDate), "00")
strDate = strDate & Format(DatePart("n", CrntDate), "00")
strDate = strDate & Format(DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5
upperbound = 0
lowerbound = 4
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Select Case intChar
Case 1
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Case Else
upperbound = 51
lowerbound = 48
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Select
strEnd = strEnd & Chr(intChar)
Next X

GenPrimaryValue = strStart & strDate & strEnd
PRONUM = GenPrimaryValue



End Function
 
G

Guest

Yes, that looks like it will generate a random number.

What would you like to do with the random number?

If you want a new random number for every line in a query,
you will have to pass one of the query fields to the function
(any field).

Public Function GenPrimaryValue(v) As String

(david)
 
V

Vincent Johns

Since I wasn't sure what you were trying to update, I wrote an Append
Query instead (or the equivalent). I didn't use a real Append Query
because Access appears to call the function only once, to save time.
That gives you the same value several times, whereas what you want is a
different random value with each new record.

I defined a Macro to call a function, StoreValues(), which appends
records containing the new random values to a Table.

[M_WriteRandom] actions:
RunCode StoreValues()

The Module contains the following three functions:
GenPrimaryValue() works the same as your GenPrimaryValue() function,
except that I moved some of the repeated code to a new function,
RandomChoice().

RandomChoice() is called by GenPrimaryValue() to generate random
numbers in a specified range.

StoreValues() appends records containing the random strings to a
Table, [Random], which contains an Autonumber field and a field
containing the GenPrimaryValue() string. For updating, you'd change
this function to use ".Edit" instead of ".AddNew", and you'd need to add
code to be sure you're updating the correct record.


[Module1] contents:

Option Compare Database
Option Explicit

Public Function GenPrimaryValue() _
As String

Dim intChar As Integer
Dim CrntDate As Date
Dim strDate As String
Dim strEnd As String
Dim strStart As String
Dim X As Long

Randomize (Timer())

CrntDate = Now
intChar = RandomChoice(89, 65)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format( _
DatePart("m", CrntDate), "00")
strDate = strDate & Format( _
DatePart("d", CrntDate), "00")
'Note: hour of the day is omitted
strDate = strDate & Format( _
DatePart("n", CrntDate), "00")
strDate = strDate & Format( _
DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5

Select Case RandomChoice(4, 0)

'Note: Case 1 is selected about 1/6 of the time
Case 1

'Note: This returns letters from "Y" to "B",
' and with "Y" and "B" each occurring half
' as often as any letters "C"-"X"
intChar = RandomChoice(89, 65)

Case Else

'Note: This returns digits from "0" to "4",
' and with "0" and "4" each occurring half
' as often as any digits "1"-"3"
intChar = RandomChoice(48, 51)

End Select 'Case intChar

strEnd = strEnd & Chr(intChar)

Next X

GenPrimaryValue = strStart & strDate & strEnd
'PRONUM = GenPrimaryValue

End Function 'GenPrimaryValue




Public Function RandomChoice( _
lowerbound As Integer, _
upperbound As Integer)

'Note: CInt() does not produce a uniform
' distribution between the given bounds, since
' it rounds fractions instead of truncating them

RandomChoice = CInt((upperbound - lowerbound + 1) _
* Rnd + lowerbound)

End Function 'RandomChoice



'This stores some generated random labels into
' the [Random] Table
Public Function StoreValues()

Const intNumberOfRecords As Integer = 5
Dim rstRand As DAO.Recordset '[Random] Table
Const strRandT As String _
= "Random" 'Table name
Dim intRec As Integer 'Counts records added

Set rstRand = CurrentDb.OpenRecordset( _
strRandT, dbOpenTable)

'Erase all existing records
DoCmd.RunSQL "DELETE " & strRandT _
& ".* FROM " & strRandT & ";"

'Add random labels to the Table
With rstRand
For intRec = 1 To intNumberOfRecords
.AddNew
!Label = GenPrimaryValue
.Update
Next intRec
.Close
End With 'rstRand

Set rstRand = Nothing

'Display the Table in Datasheet View
DoCmd.OpenTable strRandT, _
acViewNormal, acReadOnly

End Function 'StoreValues


Running [M_WriteRandom] calls StoreValues(), which erases the contents
of Table [Random], writes new records there, and displays them in Table
Datasheet View. The result looks like this:

[Random] Table Datasheet View:

Random_ID Label
----------- ------------------
-1727299056 X2006012648291133D
-821818709 X20060126482933222
-671509657 R200601264829012NE
1515091646 X20060126482932321
1910521041 T20060126482941231

For what you want to do, of course, you'd need to edit records in the
Table that you want to update, and I expect you would remove the code
that erases all the records before writing any.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
V

Vincent Johns

David's suggestion is, of course, correct and simpler than mine.
Changing your function to

Public Function GenPrimaryValue( _
v As Variant) _
As String
...

and defining an Update Query such as...

UPDATE Random
SET Random.Label
= GenPrimaryValue([Random]![Random_ID]);

where [Random_ID] is a key field in the [Random] Table, will probably do
what you want.

I'm not sure that your calculations did what you want, though, and I put
comments into the VBA code that I posted that I hope may be helpful.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
K

Kenny G

Vincent,

Thanks! It works great.
--
Kenny G


Vincent Johns said:
Since I wasn't sure what you were trying to update, I wrote an Append
Query instead (or the equivalent). I didn't use a real Append Query
because Access appears to call the function only once, to save time.
That gives you the same value several times, whereas what you want is a
different random value with each new record.

I defined a Macro to call a function, StoreValues(), which appends
records containing the new random values to a Table.

[M_WriteRandom] actions:
RunCode StoreValues()

The Module contains the following three functions:
GenPrimaryValue() works the same as your GenPrimaryValue() function,
except that I moved some of the repeated code to a new function,
RandomChoice().

RandomChoice() is called by GenPrimaryValue() to generate random
numbers in a specified range.

StoreValues() appends records containing the random strings to a
Table, [Random], which contains an Autonumber field and a field
containing the GenPrimaryValue() string. For updating, you'd change
this function to use ".Edit" instead of ".AddNew", and you'd need to add
code to be sure you're updating the correct record.


[Module1] contents:

Option Compare Database
Option Explicit

Public Function GenPrimaryValue() _
As String

Dim intChar As Integer
Dim CrntDate As Date
Dim strDate As String
Dim strEnd As String
Dim strStart As String
Dim X As Long

Randomize (Timer())

CrntDate = Now
intChar = RandomChoice(89, 65)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format( _
DatePart("m", CrntDate), "00")
strDate = strDate & Format( _
DatePart("d", CrntDate), "00")
'Note: hour of the day is omitted
strDate = strDate & Format( _
DatePart("n", CrntDate), "00")
strDate = strDate & Format( _
DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5

Select Case RandomChoice(4, 0)

'Note: Case 1 is selected about 1/6 of the time
Case 1

'Note: This returns letters from "Y" to "B",
' and with "Y" and "B" each occurring half
' as often as any letters "C"-"X"
intChar = RandomChoice(89, 65)

Case Else

'Note: This returns digits from "0" to "4",
' and with "0" and "4" each occurring half
' as often as any digits "1"-"3"
intChar = RandomChoice(48, 51)

End Select 'Case intChar

strEnd = strEnd & Chr(intChar)

Next X

GenPrimaryValue = strStart & strDate & strEnd
'PRONUM = GenPrimaryValue

End Function 'GenPrimaryValue




Public Function RandomChoice( _
lowerbound As Integer, _
upperbound As Integer)

'Note: CInt() does not produce a uniform
' distribution between the given bounds, since
' it rounds fractions instead of truncating them

RandomChoice = CInt((upperbound - lowerbound + 1) _
* Rnd + lowerbound)

End Function 'RandomChoice



'This stores some generated random labels into
' the [Random] Table
Public Function StoreValues()

Const intNumberOfRecords As Integer = 5
Dim rstRand As DAO.Recordset '[Random] Table
Const strRandT As String _
= "Random" 'Table name
Dim intRec As Integer 'Counts records added

Set rstRand = CurrentDb.OpenRecordset( _
strRandT, dbOpenTable)

'Erase all existing records
DoCmd.RunSQL "DELETE " & strRandT _
& ".* FROM " & strRandT & ";"

'Add random labels to the Table
With rstRand
For intRec = 1 To intNumberOfRecords
.AddNew
!Label = GenPrimaryValue
.Update
Next intRec
.Close
End With 'rstRand

Set rstRand = Nothing

'Display the Table in Datasheet View
DoCmd.OpenTable strRandT, _
acViewNormal, acReadOnly

End Function 'StoreValues


Running [M_WriteRandom] calls StoreValues(), which erases the contents
of Table [Random], writes new records there, and displays them in Table
Datasheet View. The result looks like this:

[Random] Table Datasheet View:

Random_ID Label
----------- ------------------
-1727299056 X2006012648291133D
-821818709 X20060126482933222
-671509657 R200601264829012NE
1515091646 X20060126482932321
1910521041 T20060126482941231

For what you want to do, of course, you'd need to edit records in the
Table that you want to update, and I expect you would remove the code
that erases all the records before writing any.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Kenny said:
Hello,

I need some help on generating a number with an update query. I have run
the query and the query runs and will generate a random number each time I
run the query. I'd like to have the numbers generated with only one click.
The below code is in a mod and the reference to the mod is in the query. I
was told I need some loop or do until code in this. Any help would be
appreciated.



Public Function GenPrimaryValue() As String
Dim strStart As String
Dim strDate As String
Dim strEnd As String
Dim CrntDate As Date
Dim intChar As Integer
Dim upperbound As Long
Dim lowerbound As Long
Dim X As Long
Randomize

CrntDate = Now
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format(DatePart("m", CrntDate), "00")
strDate = strDate & Format(DatePart("d", CrntDate), "00")
strDate = strDate & Format(DatePart("n", CrntDate), "00")
strDate = strDate & Format(DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5
upperbound = 0
lowerbound = 4
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Select Case intChar
Case 1
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Case Else
upperbound = 51
lowerbound = 48
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Select
strEnd = strEnd & Chr(intChar)
Next X

GenPrimaryValue = strStart & strDate & strEnd
PRONUM = GenPrimaryValue



End Function
Click to expand...
Click to expand...
 
K

Kenny G

David,

Thank you for your reply.
--
Kenny G


david@epsomdotcomdotau said:
Yes, that looks like it will generate a random number.

What would you like to do with the random number?

If you want a new random number for every line in a query,
you will have to pass one of the query fields to the function
(any field).

Public Function GenPrimaryValue(v) As String

(david)
Click to expand...
 
V

Vincent Johns

Kenny said:
Vincent,

Thanks! It works great.
Click to expand...

You're most welcome. As I said, some of your randomly selected values
are not uniformly distributed. If that's what you wanted, great, but if
not, consider using Int() instead of Cint() and perhaps changing the
bounds so that the lower one is listed first. (It would make a slight
difference in your results, maybe not enough to notice.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

random number generation 2
Populating a Label/Text Box with Random Number 4
SQL UPDATE Query doesn't Update Table 5
How random are randomly generated numbers ? 2
DoCmd.OutputTo wrong data type 5
Is there a better way to deal with this date? 2
Type mismatch in Function 5
winzip - rondebruin's instructions 7

Top