Emergency data correction!

J

JMorrell

I have a user who is in a tizzy about the data she has been entering this
week. She is entering data to records via a form. This data includes a
date. In the past (just last week), she was entering the month, a slash, a
day and then hitting the TAB key. This auto fills the year.

So far, so good... except that she is entering data where she should have
been entering "2004" as the year. This week, when she hit the TAB key, it
auto filled with 2005! There are hundreds of entries that are in error
because of this.

I would like to help her out by writing an update query to the effected
table but I'm not sure of the most elegant way to go about it. Can I merely
change just the 2004 to 2005 with an update query?

Any suggestions?

tia,
 
J

JMorrell

As it happens, the only entries are in December. What luck, there are no
January entries at all! (the db was brought into existance and "went live"
in September.)

I can capture the data set with a select query, but the update to just the
year part of the date field is what I'm struggling with.

Any thoughts?
JMorrell
 
J

Jeff C

Well....since you are struggling with writing an update query ( struggle with
this everyday)...I would figure a way to correct the problem a different way.
I would open in table mode.......filter some how to get to just those
records in error and then use the find/replace function..my guess is there is
some way for you to filter your table down so you can differentiate between
those december records in error and those not.
 
J

JMorrell

I looked at the dateadd function but took a different tack; probably a longer
one too.

I created a make table query to isolate my data set and created a table with
PK and date. With that table, I then did another make table query with the
date values split apart using the left(), mid(), and right() functions. With
this table I updated 2005 to 2004. Using this table, I created another field
with a new date, concatenating the fields back together again. I then made
an update query using my original table and the new table with the PK and new
date. Convoluted, but it worked.

I like the find & replace method better.

This newsgroup rocks! Thanks for all your input! Much appreciated.

JMorrell
 
Top