DAO query searching for today's date from a table.

[Frannilea]

Hi. It's been years since I've used access and I've only ever been a beginner.

I'm an English as a second language teacher writing a database to check and give practice for taught English expressions. Students basically listen to the sentences, then choose the words of the sentence from the numerous combo boxes populated with their words. The database has a "word" table containing all the words to choose from, a "sentence" table containing all their sentences and a "studentrecords" table which adds a record for each day - date, correct answers, wrong answers, candy given (they get candy for motivation), a check box for starting a new day (so they can't do a sentence more than once a day).

I'm having trouble searching for that date in the student records field. When they open the app, it needs to check for whether they've already done some that day or not, and whether they've pressed the "Prepare" button to move their sentences to the new day. I know this sounds strange, but I want the sentences to be slowly spaced between views but not specific to date, so that students can use the app any number of times a week, but not see a sentence twice on one day. So I have a field that increases (number of times before viewing again) by one every press of this button.

So I need the formload to check if a record for that date exists or not, and if it does exist, check whether that NewPrepare checkbox field has been triggered or not. The problem is that even though there is definitely a record in the StudentRecords with a ScoreDate for today, it's just not finding it - I continually get a RecordCount = 0. I vaguely remember something about different date formats, but couldn't even find a tutorial on how to search records with various dates. Please also see attachments below as pictures of the relevant information. Here is my query (note that I tried two ways to find the date) - this is a small part of the "form_load" of the Student Form:

Thanks in advance.

Dim Dailyrst As DAO.Recordset
Dim Dailydb As DAO.Database

Set Dailydb = CurrentDb
' Set Dailyrst = CurrentDb.OpenRecordset("select * from StudentRecords where ScoreDate = #" & Date & "#")
Set Dailyrst = CurrentDb.OpenRecordset("select * from StudentRecords where ScoreDate = " & Date & "")

'Set Prepare button: if a record exists for today where NewPrepare has been triggered, then disable the Prepare button


If Dailyrst.RecordCount = 1 Then
If Dailyrst!NewPrepare = True Then
Me.BtnPlay.SetFocus
Me.BtnNewDay.Enabled = False
End If
End If

'If there is no record created for today, then create a new one.
If Dailyrst.RecordCount = 0 Then
Dailyrst.AddNew

Dailyrst!ScoreDate = Date
Dailyrst!CorrectAnswers = 0
Dailyrst!WrongAnswers = 0
Dailyrst!candyGiven = 0
Dailyrst!NewPrepare = False
Me.BtnNewDay.Enabled = True
Me.BtnNewDay.SetFocus

Dailyrst.Update
End If


Dailyrst.Close
Set Dailyrst = Nothing
Set Dailydb = Nothing

[Bob Fitz]

Perhaps you need () after Date:

CurrentDb.OpenRecordset("select * from StudentRecords where ScoreDate = #" & Date() & "#")

[CJ_London]

Try

where ScoreDate = Date()")

[Frannilea]

Thank you! That worked!!