ACC2002: How to Generate Random Dates (304085)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304085
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

By using the Rnd function in Visual Basic for Applications (VBA), you can generate a random date value. This may be useful for generating sample data for your application or for testing values. This article shows you two functions that you can use to generate random dates.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
The two functions included are:
  • RandomDateInRange Generates a random date within a specified range.
  • RandomDate Generates a random date between December 30, 1899 and the current date.

Sample Code

  1. Start Microsoft Access, and then open the database that you are working with.
  2. Click Modules under Objects, and then click New to insert a new module.
  3. Type or paste the following code in the module:
    Function RandomDateInRange(LowerDate As Date, UpperDate As Date) As Date
    RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Function RandomDate() As Date
    ' multiply the Rnd function * date() + 1 to prevent dates
    ' in the future
    RandomDate = Int(Rnd() * CDbl(Date + 1))
End Function
  4. On the File menu, click Save <ProjectName> to save the module, where <ProjectName> is the name of your Visual Basic for Applications project.
  5. Type the following lines in the Immediate window, and press ENTER after each line:
    ? RandomDateInRange(#1/1/2001#, #12/31/2001#)
    ? RandomDate()

REFERENCES

For more information about generating random numbers, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type rnd function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about date values, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type date data type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto KB304085
©2004 Microsoft Corporation. All rights reserved.