SQL Upsize affecting date fields

[Mr Clevver]

Hi all,

Not sure this is in the correct area, as the issue covers a multitude of areas, but here goes:

I have an issue that has started occurring after I upsized my system to run on an SQL backend.

I have a form (fMainMenu) which has a textbox (txtDate). The forms also contains seven subforms which reference a table called tEvents.
txtDate contains the value Date() and can be altered by three buttons, which push the date forward 7 days, backward 7 days or return to todays date.
Each of the 7 subforms displays the events contained within tEvents, corresponding to a specific day of the week, by means of limiting their data with the expression:
Mon
EventDate = [Forms]![fMainMenu]![txtDate]+(2-Weekday(Date()))
Tue
EventDate = [Forms]![fMainMenu]![txtDate]+(2-Weekday(Date())) +1
Wed
EventDate = [Forms]![fMainMenu]![txtDate]+(2-Weekday(Date())) +2
etc.

This seemed to work very well until the afore mentioned upsize to SQL, but now the all the subforms seem to be returning all the events for 2 days in the future (i.e. Monday displays Wednesday's etc.). Interestingly, the form header for each subform, which contains a textbox set to display
[Forms]![fMainMenu]![txtDate]+(2-Weekday(Date())) still displays correctly. This makes me think that there is some difference in the way SQL handles requests based on date information.

Preferably I'd like to understand why it is that SQL is doing this, but failing that anything that gets the system working the way it should would be appreciated.

Thank you in advance.

Edit - An obvious work-around, which has just struck me, in the form of simply adding -2 to the end of each date expression:
Mon
EventDate = ([Forms]![fMainMenu]![txtDate]+(2-Weekday(Date()))) -2
seems to be working, but it's clunky, and I'd still rather understand why it's happening.

 

[LPurvis]

An odd scenario - in that the difference sounds entirely like the difference between the base dates in SQL Server and Jet. (1900-01-01 and 1899-12-30 respectively).

However it's the role of ODBC to make the conversion - have you checked your ODBC version (and then updated it)? Are you using the SQL Server driver or the Native Client, and upon what operating system?
I'm assuming here that you are still using linked tables?
If not then what code?

Cheers.

 

[Mr Clevver]

Thanks for the response.

I'm using the SQL server driver, version number (2000.85.1132.00). I can't see any obvious way to update this driver, however. I've briefly checked Google, though this turns up nothing obvious.
The client OS is XP, server is Windows 2000 Advanced Server.
Yes, the client uses linked tables to make the connection to the Server.

 

[LPurvis]

Sorry for the delay in response.
Have you considered switching the the SQL Native provider? (You can specify that driver when establishing your DSN, if that's how you connect - or if you build your table links via code then it's even simpler).

Cheers.