Go Back   Access World Forums > Apps and Windows > Other Software

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-05-2018, 07:50 AM   #1
Soot
Newly Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Soot is on a distinguished road
Dates change by a week from Access to SPSS

I am experiencing a curious issue when importing data from Access 2016 (32-bit) to SPSS 24 (32-bit). The Access database contains historical dates, ranging from 1582 to 1800. Although SPSS correctly recognizes each variable that has a date-property (and therefore does not label them with a string-property), the program systematically introduces a change in the dates between the two programs. For example, the 6th of December 1729 in Access is converted in SPSS as the 30th of November 1729. The difference between the dates are the largest in 1582 (42 days) and then decreases by a day every four years until the difference is the smallest in 1753 (1 day). After 1753, the Access-dates are correctly transferred into SPSS.

Firstly, I do not know why SPSS has wrongfully labelled 1581 (and subsequently 1585, 1589, etc.) as leap years. Historically, the leap years were 1584, 1588, etc. Second, I do not understand why SPSS has introduced this issue and leaves it be until the situation corrects itself in 1753. Is there a change I can make in Access to prevent this issue? Is there a way to correct SPSS and its understanding of leap years? Is there another solution to this issue?

Thanks in advance!

Soot is offline   Reply With Quote
Old 04-05-2018, 12:51 PM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,484
Thanks: 71
Thanked 1,105 Times in 1,030 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Dates change by a week from Access to SPSS

Here is your history lesson ....

The reason is almost certainly caused by the calendar changing from Julian to Gregorian.

Much of Europe changed in 1582 but the UK didn't like these new fangled ideas and didn't change till 1752.
When the UK finally did so 11 days were 'lost' form the calendar leading to the 'Give Us Our Eleven Days' calendar riots of 1752.

For example, see https://www.historic-uk.com/HistoryU...r-eleven-days/

I suspect that Access & SPSS have used different criteria for determining the correct dates between 1582 and 1752
As to how you convert correctly, you'll either need to write a conversion algorithm or hope someone else has already done so.
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 04-05-2018, 01:08 PM   #3
Soot
Newly Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Soot is on a distinguished road
Re: Dates change by a week from Access to SPSS

That sounds very credible, thank you for this interesting suggestion (and history lesson ).

It is a shame, however, that this historical reality introduces incorrect data when transferring my Access-data to SPSS..

Soot is offline   Reply With Quote
Old 04-05-2018, 01:21 PM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,484
Thanks: 71
Thanked 1,105 Times in 1,030 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Dates change by a week from Access to SPSS

Quote:
Originally Posted by Soot View Post
That sounds very credible, thank you for this interesting suggestion (and history lesson ).

It is a shame, however, that this historical reality introduces incorrect data when transferring my Access-data to SPSS..
Strictly speaking it hasn't because the 'correct' date just depends on whichever calendar has been chosen as being 'correct'.
If you want to retain the dates from one program to the other, suggest you save the dates as text then convert back after importing.
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 04-05-2018, 01:25 PM   #5
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,533
Thanks: 10
Thanked 1,242 Times in 1,182 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by Soot View Post
It is a shame, however, that this historical reality introduces incorrect data when transferring my Access-data to SPSS..
WARNING: Reality is chaotic, changes without warning, and is not suitable for everyone. Talk to your doctor or health professional before attempting to represent reality in a structure data system.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
Minty (04-06-2018)
Old 04-05-2018, 10:39 PM   #6
Soot
Newly Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Soot is on a distinguished road
Re: Dates change by a week from Access to SPSS

Quote:
Originally Posted by ridders View Post
Strictly speaking it hasn't because the 'correct' date just depends on whichever calendar has been chosen as being 'correct'.
If you want to retain the dates from one program to the other, suggest you save the dates as text then convert back after importing.
I will do so, thank you again!

Soot is offline   Reply With Quote
Reply

Tags
access , dates , spss

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Week End and Week Start dates for a given Month dwood@atx Reports 12 10-19-2016 05:08 PM
Interfacing Access to SPSS DavidG3276 General 1 12-24-2013 12:48 AM
Comparing all dates in query to each other to find dates within a week of each other Chase.Wichert Queries 1 03-30-2012 05:18 AM
Designing an access form and copying to SPSS. Please help rubicond Queries 3 08-12-2007 02:28 AM
importing SPSS data into ACCESS sha7jpm General 0 10-04-2002 01:30 AM




All times are GMT -8. The time now is 05:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World