Check if the date selected is found in a table in access-Need right sytex

Sivasan used Ask the Experts™
Hi There,

In an click event in an access form, I'm checking to see a date entered in the form is found in a year table, if
so I perform certain function. I see that though the date is present in the table, the code doesn't catch it. I believe I'm using the wrong syntex when I check for the date.

Dim Ndate As Date
Ndate = CDate(Format(Me.enterdate, "mm/dd/yyyy"))
Set rs = CurrentDb.OpenRecordset("select * from Yeartbl where Working = " & Ndate)

If rs.EOF = True Then
 ' The condition for end of record true gets executed

' condition for date present gets executed.

I see that always
 ' The condition for end of record true gets executed
even if date is present in yeartbl.
I'm guessing & Ndate is not the right syntex. I will appreciate if somebody can tell me the right one.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013
change your select

"select ....working=#" & ndate & "#"

Using # around the date
If Working is a date field then you need to use the pound sign in your SQL string

Set rs = CurrentDb.OpenRecordset("select * from Yeartbl where Working = #" & Ndate & "#")

You could also do this without using a recordset:
If dcount("*","Yeartbl","[Working]=#" & Ndate & "#")=0 then
    'date exists in table
    'date is not in table

Is the field "Working" a date type field?
Is the table in an access file or is it a SQL table?
Also I would try changing these two lines:
1.  "If rs.EOF = True then " to  "If rs.BOF  And rs.EOF then".
2.  Change your select statement to- "SELECT * FROM Yeartbl WHERE Working = #" & Ndate & "#"

If the field is a date type field, the '#' will signify a date in SQL string.

mbizup and IrogSinta are correct.  They posted as I was typing.


Worked. Thank you so much I remembered # I couldn't remember the '

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial