easy DATE problem??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot seem to get something that I thought would be
easy to work.

I have an update query putting the current date and time
in a field called LastUpdate. At the same time, I would
like the ExpirationDate field to be updated with exactly
one year from the LastUpdate.

I tried DateAdd and couldn't get it to work. How do I do
this in the update query?
 
Unless you are doing the update from a form, you'll have
to use two update queries. When you update the LastUpdate
field, the Expiration Date field reads the old LastUpdate
value. Running a second update query to change the
Expiration date would fix the problem.

Or

If you are using input fields on a form that feeds the
contents to the update query, you could use a textbox to
caldulate the Expiration date with the dateadd function
and then the query would use that calculated value to
update the table.
 
I cannot seem to get something that I thought would be
easy to work.

I have an update query putting the current date and time
in a field called LastUpdate. At the same time, I would
like the ExpirationDate field to be updated with exactly
one year from the LastUpdate.

I tried DateAdd and couldn't get it to work. How do I do
this in the update query?
Click to expand...

It would be like this:

DateAdd("yyyy",1,Now())

Seemed to work for me. I think the Now() function could be a
reference to the other field.
 
That is exactly how I wrote it and it doesn't work. Plus,
whenever I put the quotes around the yyyy, I receive a
compile error: expected end of statement. I will try a
second update query as Ken suggested...hopefully that will
work.

Thanks for the suggestions.
 
Oh... I have Access 2000 and I just put the function into the query
grid, and all the records had current date and "2005" for the other
field's year...

hummm...
 
I am using Access 2000 as well. I am obviously doing
something wrong. here's my SECOND query I added to update
only the ExpirationDate. it's still not working...

query = "UPDATE StatusTracking INNER JOIN
temp_YearlyRecert as T " & _
"ON StatusTracking.RequestID = T.RequestID " & _
"SET StatusTracking.ExpirationDate = DateAdd
("yyyy",1,Date()) " & _
"WHERE (((StatusTracking.RequestID) =
T.RequestID));"
DoCmd.RunSQL (query)

With the quotes around yyyy, I am getting a error expected
end of statement. If I take the quotes out, it prompts
for a parameter value for yyyy.

Any other suggestions?
 
On Fri, 20 Feb 2004 08:51:17 -0800,

In your string try to put this exactly where your function is:
DateAdd("&""""&"yyyy"&""""&",1,Date())

I know in VBScript the 4 quote pattern will create 1 quote when
interpreted in the string...

if that doesn't work just try:
DateAdd("&"""&"yyyy"&"""&",1,Date())

A 3 quote pattern.

Good luck.
 
To close the loop for anyone else reading this... I have
figured out my problem. Even though the documentation
shows double quotes around the interval which is YYYY in
my case, it works perfectly with single quotes.
 
Back
Top