CODEWITHSUNDEEP
asp.netvb.netms-access
user1100149
user1100149

Reputation: 266

ASP - Struggling with date format (in WHERE clause) when fetching records from MS Access DB

In the process of moving an existing (and previously working) site from Windows Server 2003 to Server 2008, jumping from IIS6 to IIS7, I have come across this oddity:

OpenSQL("SELECT * FROM bookings WHERE apartment_id = '" & strApartment & "' AND BookDate = #" & SwitchDate(dDate) & "#")

When run, the above SQL script only returns rows if there is no leading zero in the date. For example, it will return rows if the date is 10/01/2010. But it will not return rows if the date is 01/01/2010. Instead I get this error:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I'm not very familiar with ASP at all (in this instance running VB I believe, though it me be classic ASP - I'm not sure how you tell?). But this is a very simple request and I can't understand what is going on. I have tried removing the switchdate function (which removes all leading zeros), and playing with the syntax, all to no avail.

The Access DB holds the records in the format 01/01/2001.

Upvotes: 1

Views: 635

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

While it is true that the ACE and Jet database engines treat ambiguous #xx/yy/zzzz# date literals as mm/dd/yyyy they also accept the unambiguous formats yyyy-mm-dd and yyyy/mm/dd so your best solution would be to update your SwitchDate() function to return one of those formats.

Upvotes: 1

Related Questions