none
Randomised dates not falling within set range RRS feed

  • Question

  • I've been tasked with modifying the code of our random customer generator for use in testing. And I'm having trouble getting the randomly generated dates to fall in the correct range. The code I'm using is below, and for the life of me I can't figure out why I always get a few outliers which aren't in the range, and they're always random record numbers, so it's not a particular record number causing this.

    Sub M016()
    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    Dim strSQL As String
    Dim UDate As Date
    Dim LDate As Date
    Dim i As Integer
    Dim NDate As Date
    Dim PV As String
    Dim CV As String
    Dim CV2 As String
    Set db = CurrentDb
    
    'M016 AntenatalAppDate
    For i = 0 To 8
    PV = CDate(Nz(DLookup("[M016]", "[CustomerDetails]", "[M026] = 'MOT000000" & (i + 1) & "'")))
    UDate = strGlobalRPEnd
    LDate = DateAdd("m", -12, strGlobalRPEnd)
    'If DateDiff("m", LDate, PV) > 12 Or DateDiff("d", PV, UDate) < 0 Then
        Do
        NDate = RandomDateInRange(LDate, UDate) 'And DateDiff("d", LDate, NDate)
        'MsgBox DateDiff("d", NDate, UDate)
        Loop Until DateDiff("d", LDate, NDate) > 0 And DateDiff("d", NDate, UDate) > 0
        strSQL = "UPDATE [CustomerDetails] Set [M016] = " & "#" & NDate & "#" & " WHERE [M026] = 'MOT000000" & (i + 1) & "'"
        db.Execute strSQL
    
        If DateDiff("d", LDate, NDate) > 365 Then
            i = i - 1
            End If
    Next
    
    For i = 9 To intGlobalRecords
    PV = CDate(Nz(DLookup("[M016]", "[CustomerDetails]", "[M026] = 'MOT00000" & (i + 1) & "'")))
    UDate = CDate(strGlobalRPEnd)
    LDate = CDate(DateAdd("m", -12, strGlobalRPEnd))
    
        Do
        NDate = RandomDateInRange(LDate, UDate)
        Loop Until DateDiff("d", LDate, NDate) > 0 And DateDiff("d", LDate, NDate) < 365
        strSQL = "UPDATE [CustomerDetails] Set [M016] = " & "#" & NDate & "#" & " WHERE [M026] = 'MOT00000" & (i + 1) & "'"
        db.Execute strSQL
    
            If DateDiff("d", LDate, NDate) > 365 Then
            i = i - 1
            End If
    
    Next
    End Sub

    This is the RandomDateInRange function being used:

    Function RandomDateInRange(LowerDate As Date, UpperDate As Date) As Date
    
    RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
    End Function

    If anyone can help it would be very much appreciated.

    Many thanks to Mogul and Ciprian for the advice there, but I discovered my problem. For some reason access was changing all the dates where the month and day were below 12. I got round it by converting the randomised date into a string before putting it into SQL. Obviously this isn't ideal for those who need the the relevant fields to be in date/time format, but it works for my purposes.


    • Edited by AB19842 Wednesday, December 30, 2015 9:07 AM
    Tuesday, December 29, 2015 11:18 AM

All replies