Emergency data correction!

[Guest]

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,

 

[Joseph Meehan]

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,

As I see it, your problem is going figuring out how you are going to
identify which January 3rd is 2004 and which is 2005.

 

[Guest]

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

 

[James Hahn]

The dateadd function is the one you need.

 

[Joseph Meehan]

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

As James noted the dateadd function should work fine.

 

[Guest]

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.

 

[Guest]

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