MS Access97 SQL UPDATE

[JoeP]

I would appreciate a tip on looping through A MS Access Table with an "UPDATE" query to check the following condition:

If Today's Date Is 30 Or More Days Than Date3.

If True Then:

I would like to shift Dates & Values. Set Date2 & 3 + Int Fields to "" & 0 Respectively.

Update Date1 To Date2
Update Field2 To Field5
Update Field3 To Field6

Update Date2 & Date3 = ""
Update Fields5-6 8-9 = 0

[Field1]Date1
[Field2]Int1
[Field3]Int1
[Field4]Date2
[Field5]Int2
[Field6]Int2
[Field7]Date3
[Field8]Int3
[Field9]Int3


Could someone modify this SQL statement the most efficient way:

SQLstmtQA = "UPDATE [History]"
SQLstmtQA = SQLstmtQA & " Set [Field7]="",[Field8]=0,[Field9]=0"
SQLstmtQA = SQLstmtQA & " WHERE [?]
Set rsQA = connQA.execute(SQLstmtQA)

TIA For ANY Suggestions or Tips.

[raf]

I think you need something like this. (not tested + don’t know Access 97, but this should be the correct syntax.

Sql = UPDATE history SET field1=Null, field2=0, etc WHERE Date3 > #thedate#
Sql=replace(sql,”thedate”, DateAdd("d", 30, Date())


The etc off coarse needs to be changed . The ‘thedate’ is replaced by the result of a function that adds 30 days to the present date. But maybe you want to have the date + 1 month or + 4 weeks or whatever.

Here’s some more info on the dateadd function.
DateAdd Function Language Reference Version 2
See Also

Description
Returns a date to which a specified time interval has been added.
Syntax
DateAdd(interval, number, date)
The DateAdd function syntax has these parts:
Part Description
interval Required. String expression that is the interval you want to add. See Settings section for values.
number Required. Numeric expression that is the number of interval you want to add. The numeric expression can either be positive, for dates in the future, or negative, for dates in the past.
date Required. Variant or literal representing the date to which interval is added.
Settings
The interval argument can have the following values:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week of year
h Hour
n Minute
s Second
Remarks
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now. To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
The DateAdd function won't return an invalid date. The following example adds one month to January 31:
NewDate = DateAdd("m", 1, "31-Jan-95")
In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.
If the calculated date would precede the year 100, an error occurs.
If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.

[JoeP]

Thank You I will experiment with this and see if any luck.