Solved

Date problem in access

Posted on 2011-02-23
9
352 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
9 Comments
 
LVL 21
Comment Utility
How will you know the correct date?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 125 total points
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Paulsburbon
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now