Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date problem in access

Posted on 2011-02-23
9
Medium Priority
?
366 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 500 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 500 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 500 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

636 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