Solved

Date problem in access

Posted on 2011-02-23
9
360 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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