The control goes to EOF

[aman1234]

Hi guys

If I select the date from 06/01/2012 to 10/01/2012 then the control goes to EOF and displays message No Record,operation cancelled but in actual in the database there is a record with 06/01/2012 date. And if I select 01/01/2012 to 10/01/2012 then it works fine.

Can anyone please figureout my problem?

Please Login or Register  to view this content.

[Bmoe]

I would take a look at your SQL statement. Set a breakpoint at the rs.Open line and take a look to make sure that your SQL statement looks exactly the way you think it should. SQL is extremely picky in how the line is parsed.

For example,

Please Login or Register  to view this content.

Part of the string would read "#01/01/2012 # AND RecDate <= # 01/12/2012 #" You have a space between the # and the date everywhere but the very first pound sign. That might cause an issue.

You also might want to block your date selection in () and then AND document

SELECT * FROM tbl WHERE(RecDate >= #D1# AND recDate <= #D2#) AND document = 'a'

I think there is a buit in SQL statement BETWEEN D1 and D2 but you would need to test that because sometimes BETWEEN returns differently from providers. I don't know how JET does it. I use BETWEEN quite a bit but I also have a time stamp so when I say BETWEEN 01/09/2012 and 01/10/2012 there are a bunch of records from 12 to 12.

Of course this might all be pedantic nonsense and not help you. I have in the past durring debug taken the SQL statment and pasted it into Access' query builder and debuged it from there when I have gotten stuck.

[aman1234]

Hi Bmoe,

Thanks for your reply. I m using a message box and if I select 01/01/2012 and 10/01/2012 then it picks up the right date in the string but why it goes to EOF then?

Please Login or Register  to view this content.

[aman1234]

but if i change it to as follows then it works:

Please Login or Register  to view this content.

[Kyle123]

Hi aman,

You should never be passing dates into SQL queries in that format, they should always be passed as yyyy-mm-dd

Start by changing this and see if it makes any difference. Access will quite happily accept regional date settings, but Jet generally doesn't.