Solved

Date problem in access

Posted on 2011-02-23
9
362 Views
Last Modified: 2012-08-13
I mis coded a sql statement in a form that I didn't catch until a month afterward.  Instead of "#Date()# I used "'Date()'" to insert the current date. Most of those dates are 6/22/1894 and then count downward.  I was going to loop thru the table and try to convert them to the correct date that it should have been but I have no clue where to start. Thanks for the help!
0
Comment
Question by:Paulsburbon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 21
ID: 34962281
How will you know the correct date?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 125 total points
ID: 34962301
You don't need the # delimiters around the Date() function -- just around date strings like
#2/12/2002#.  Just use Date() by itself, like this:

GetStartDate = DateAdd("d", -2, Date)

(the parentheses will be stripped off)

As far as the wrong dates are concerned, you will need to create an update query using DateAdd to increment them by the appropriate amount; hopefully they are out of date by a consistent amount of time.
0
 

Author Comment

by:Paulsburbon
ID: 34962424
I honestly was hoping someone had done this in the past.  It took the date() function and got some data from it.  It then indserted it into the table and took what ever it got and thought it was 120 years ago.  I was hoping someone could tell me how to convert that 6/21/1894 into the original data and then I could reinsert it but be the correct date.  Does that make any sense? I think they are consistent but not sure.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Paulsburbon
ID: 34962443
I'll add I fixed the problem so it is not doing it anymore but I have 2000 rows of old data I need to fix.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34962530
you can run an update query like this

update tablex
set [datefield]=dateadd("yyyy",120,[dateField])
where year([datefield]) < 1900


create a backup copy of the table before running the update query


0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 34962564
adding 120 years to the date 6/21/1894  will give a date in the future > 6/21/2014

so better, do some analysis first on how many years you need to add to correct the problem,

run this select query to view those records

select * from tableX
where year([datefield])< 1900

0
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 125 total points
ID: 34962586
Do you have the original SQL construction? You seem to be saying that you had mis-constructed SQL that did not surround a date value with #s before inserting it, like:

INSERT INTO tablename (datefield) VALUES (1, 2/24/2011);

instead of

INSERT INTO tablename (datefield) VALUES (1, #2/24/2001#);

However, in this setup I can't see how you'd get 6/22/1894, just date values of December 30, 1899 (date value = 0) because
month number divided by day number divided by year number
should always result in a number between 0 and 1.
6/22/1894 equals date value -2107. I can't image what mal-formed date syntax would create that.

What does ? Date() show if run in the immediate window?
0
 

Author Comment

by:Paulsburbon
ID: 34962666
My original was with single quotes.

So it was something like strSQL = UPDATE table 1 SET dtmdate = '" & Date() & "' Where ID = ID;"
That is not actually it but with some short hand.

I might have used Date$() to make it a string

Date() comes back 2/23/2011
Date$() comes back 2-23-2011
 
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 34962854
Then you have inserted the numeric value of 2-23-2011= -2032 days.

Try running a select query with this expression to see if it will return likely dates:

TrueDate: DateAdd("yyyy", 2011-1894, CDate(CDbl([YourDateField])))

If so, run an update query using this expression.

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question