?
Solved

Date problem in access

Posted on 2011-02-23
9
Medium Priority
?
365 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 51

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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