asked on

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

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
else

' 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.
thanks

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IrogSinta

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
else
'date is not in table
endif

trbaze

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.

trbaze

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

Sivasan

ASKER

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