Basic question, urgent help needed.

[fishnu]

I don't have time to learn Access right now, but have an urgent problem and need help. I have a table with a variable , "Date" with several thousand values. Due to an error on my part the most recent 2000 have to be set three weeks earlier (-21) and then saved under the same variable name. The three intervening weeks of dates have no values currently so there will be no naming conflict.



I can open the database and edit them one by one but need a simple process for doing this to an entire range.

Thanks!!

[RuralGuy]

I don't have time to learn how to drive right now but can I borrow your car so I can take a 1000 mile trip by myself?

[fishnu]

LOL Ruralguy. Thanks for your assistance. I really needed help and was looking for advice. Your advice let me know this forum is not the place to get it.

"You might be a red neck if.... your family tree doesn't branch"

[RuralGuy]

I couldn't resist. I don't mind helping but you do understand we do not speak the same language yet. ie: Tables do not have variables.

[RuralGuy]

Do you really mean the Field in the table is named "Date"? If so then you should be aware that Date is a reserved word in Access and should not be used for your name for anything. Setting a Date field back 3 weeks or 21 days is not difficult but is there some way to identify the records other than the last 2000 entries?

[fishnu]

I have a Coral Reef Tank controlled by a device called an Aquacontroller 3 and software called Aquanotes.

AquaDB is a database for the device and the accompanying program. Aquanotes polls the IP of the Aquacontroller regularly for several tank conditions and events. Based on observed values, the Aquacontroller turns certain (X10) devices connected to the Reef Tank on and off.

Date is employed in several tables, including two in particular "EventLog" and "TankConditions". All fields are created and populated by the program, none by me.

In these two tables all records possessing a Date field value of greater than or equal to 3/25/09 00:00:00 are off by 18 days. That is, 3/25/09 11:00:00 AM should be 3/07/09 11:00:00 AM.

I need to subtract 18 from each record's Date field where the value is greater than 3/24/09 and save it back into the record.

Is this possible to do systematically?

[RuralGuy]

Simple answer is yes but I'm not an SQL wizard so I will need to practice a bit. Start by making a backup of your db so you can restore if we blow it. The use the query builder to create a SELECT query to zero in on the criteria. Any chance you can email me the db or a sample of it so I can practice? I don't think your post count is high enough to just attach it here.

[RuralGuy]

Something like:
SELECT * FROM YourTable WHERE YourTable.DateField > #3/24/09#

[RuralGuy]

Something like:
UPDATE YourTable SET YourTable.DateField = DateAdd("d",-18,YourTable.DateField)
WHERE DateValue(YourTable.DateField) > #3/24/09#
... It will be necessary to just look at the DateValue since any time after midnight will be > than just the date.
Here's a link that describes the DateTime field.

[Matrix]

RuralGuy: fishnu replied to the forum notification email with the db file. So I just upload it directly other than tell him to send it again.

P.S. There are no upload restrictions for new members.

[RuralGuy]

Thanks for the assist and info Matrix.

[RuralGuy]

Put the following text (please copy and paste) in a new query in SQL view.

UPDATE TankConditions SET TankConditions.[Date] = DateAdd("d",-17,[Date])
WHERE (((TankConditions.Date)>#3/24/2009#));

save the query and then double click it from the database window. Be sure and run this on the BackUp RIGHT? if it does what you want then in the real db just rename the table and then inport the modified table from the backup. Better safe than sorry. Let us know how you make out.

[RuralGuy]

BTW, the picture you posted is Access 2007 but the file seemed to be in Access 95 format. Is that correct?