Access SQL Randomizer Not working as intended

Question

I'm using the below mentioned code to select a record ID from an Access Database that wasn't already selected in the last day and add it to an array.

The general goal is that a record that matches the initial "Difficulty" criteria will be retrieved so long as either the record was never selected before OR the record wasn't chosen in the last 2 days. After the loop is done, I should have x amount of unique record ID's and add those onto an array for processing elsewhere.

 Private Function RetrieveQuestionID(questionCount As Integer)

    ' We're using this retrieve the question id's from the database that fit our arrangements.
    Dim intQuestArray(0 To questionCount) As Integer
    Dim QuestionConnection As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = |DataDirectory|\Database\MillionaireDB.accdb;")

    QuestionConnection.Open()

    For i As Integer = 1 To intNoOfQuestions
        'TODO: If there are no valid questions, pull up any of them that meets the difficulty requirement....

        Dim QuestionConnectionQuery As New OleDb.OleDbCommand("SELECT Questions.QuestionID FROM Questions WHERE (((Questions.QuestionDifficulty)=[?])) AND (((Questions.LastDateRevealed) Is Null)) OR (Questions.LastDateRevealed >= DateAdd('d',-2,Date())) ORDER BY Rnd((Questions.QuestionID) * Time());", QuestionConnection)
        QuestionConnectionQuery.Parameters.AddWithValue("?", intQuestionDifficulty(i - 1).ToString)
        Dim QuestionDataAdapter As New OleDb.OleDbDataAdapter(QuestionConnectionQuery)
        Dim QuestionDataSet As New DataSet

        QuestionDataAdapter.Fill(QuestionDataSet, "Questions")
        intQuestArray(i - 1) = QuestionDataSet.Tables("Questions").Rows(0).Item(0)

        Dim QuestionConnectionUpdateQuery As New OleDb.OleDbCommand("UPDATE Questions SET Questions.LastDateRevealed = NOW() WHERE Questions.QuestionID = [?]", QuestionConnection)
        QuestionConnectionUpdateQuery.Parameters.AddWithValue("?", intQuestArray(i - 1).ToString)
        QuestionConnectionUpdateQuery.ExecuteNonQuery()

    Next

    QuestionConnection.Close()
    Return intQuestArray
End Function

However, looping through the array will show that there are records are somehow being repeated even though the record updates during the loop.

Is there another way to loop through the database and pull up these records? I even attempted to move the .Open() and .Close() statements to within the For...Next loop and I'm given worse results than before.

_OR the record wasn't chosen in the last 2 days_ look critically at that **>=** — Steve, Aug 14 '15 at 19:28

score 1 · Accepted Answer · edited May 23 '17 at 12:06

As Steve wrote, the >= should be a < .

In addition, your WHERE clause is missing parentheses around the OR part. It should be (without all unnecessary parentheses):

SELECT Questions.QuestionID 
FROM Questions 
WHERE Questions.QuestionDifficulty=[?] 
  AND ( Questions.LastDateRevealed Is Null 
     OR Questions.LastDateRevealed < DateAdd('d',-2,Date()) ) 
  ORDER BY Rnd(Questions.QuestionID * Time());

Also have a look at How to get random record from MS Access database - it is suggested to use a negative value as parameter for Rnd().

Access SQL Randomizer Not working as intended

1 Answers1