data migration from MS Access to sql server - date issue (1 Viewer)

Derek

Registered User.
Local time
Today, 15:19
Joined
May 4, 2010
Messages
234
Hi guys,

I have migrated tables from MS Access to sql server and when I tried to run the forms (which are on MS Access) then they work fine most of the times but where there is date filter in the code it fails . Please see below the code (I had to format the date to make it working otherwise it wasn't pulling through any records). Just wondering what date format does sql server accepts and is there any other workaround for this?

Code:
 Set rs = CurrentDb.OpenRecordset("select Purpose,Direction,Outcome,Date_Feedback from qry_FormsCat_SignOff_Audits_All WHERE Date_Feedback >= #" & Format(Forms!frm_FormsCat_SignOff!txtStartDate, "M/D/YYYY") & "# AND AgentRef= '" & Forms!frm_FormsCat_SignOff!cboStaffNumberRef & "'", dbOpenDynaset, dbSeeChanges)

Many Thanks,
Derek
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:19
Joined
Feb 19, 2002
Messages
42,976
SSMA defaults to date data types that Access can't "see". Once you know this, you can change the defaults ahead of time before you do the next conversion.

Check the date field data type on the server. If it is not DateTime, Access is probably interpreting the column as text rather than a date so it sees the date as 02-04-2020 and consequently it will compare and sort as a string rather than a date. That means that 12-3102019 is greater than 02-04-2020 because strings are compared (sorted) character by character, left to right.

Change the data type to DateTime OR update your SQL Server driver. The default SQL Server driver is equivalent to SQL Server 2005 and any datatype added after that either causes an error or is rendered as text. If you update the driver, you MUST also update the driver for ALL users as well.
 

Users who are viewing this thread

Top Bottom