Macro to update dates in table (add 7 days)

[Melanie_H]

I have a table where users, in the date field, type in the date of an appointment.

What I want to do is, next week, be able to run a macro that will automatically add 7 days to that date.

Is that possible to create for the table?

Thanks for any suggestions or ideas!

 

[ajetrumpet]

you add 7 days like this:

dateadd("d", 7, valueToAddTo)

a macro is not really needed. only 1 line of code needed. best to put behind a simply button

 

[Rich]

I have a table where users, in the date field, type in the date of an appointment.

What I want to do is, next week, be able to run a macro that will automatically add 7 days to that date.

Is that possible to create for the table?

Thanks for any suggestions or ideas!

The question has to be why would you want to?

 

[Melanie_H]

dateadd("d", 7, valueToAddTo)

How do I create a button and have it on the table?
(I'm not sure how to make this happen so I do appreciate your help.)
Thank you.

Melanie

 

[Melanie_H]

dateadd("d", 7, valueToAddTo)

Can you tell me how to make the button on the table?
I'm not sure what do do with the code above?

Thank you.... I should be able to figure this out.... so I appreciate your help.

Melanie

 

[Melanie_H]

can you tell me how to add the code to the button?
dateadd("d", 7, valueToAddTo)
I can't seem to figure out how to get a button on the table.

Thank you..... very much.

Melanie

 

[ajetrumpet]

can you tell me how to add the code to the button?
dateadd("d", 7, valueToAddTo)
I can't seem to figure out how to get a button on the table.

Thank you..... very much.

Melanie

you can't add buttons to tables melanie. buttons are form and report elements only. it is a good idear not to use functions in tables anyway. modify your data by using forms and code. put a button on a form. behind it, put this code:

docmd.setwarnings false
   docmd.runsql "UPDATE table SET [fieldname] = dateadd("d", 7, [fieldname])
      [COLOR="Green"]WHERE [fieldname] = CriteriaHere[/COLOR]"
docmd.setwarnings true

the code in green is optional. does that make sense?

 

[Melanie_H]

Thank you.

I have the field from my Table now on a form. The date field is called "Appt_Date"
The type of field is Date/Time and the format is Short Date.

I draw the button, right click on it and go to build event/code builder.
The following is the visual basic code:
Private Sub Command14_Click()
DoCmd.SetWarnings False
docmd.runsql "UPDATE table SET [Appt_Date] = dateadd("d", 7, [Appt_Date])
DoCmd.SetWarnings True
End Sub

It keeps highlight the "d" and telling me there is a compile error.

I've been searching but am stumped.....
any ideas or thoughts on where I goofed.

Thanks.
Melanie

 

[Melanie_H]

one more thing to do..... I do need it to update all the Appt_Date fields in the entire table all at once.

Is there a better way to do this?
If it is on the form.... does that only update that one form?

Thanks as always.
Melanie

 

[Melanie_H]

I think I have it now.... disregard my questions.

I am going to have them run an "update" query on the Appt_date field
and then I input the DateAdd function there.

Thank you for the DateAdd information...... highly appreciated.

Thanks.
Melanie H.

 

[pbaldy]

For the record, your error was due to double quotes around the d (the first ones closed off the string that was started at UPDATE) and no quotes at the end:

docmd.runsql "UPDATE table SET [Appt_Date] = dateadd('d', 7, [Appt_Date])"

Double quotes around the d would be fine outside VBA, but they confuse things inside it.

 

[Melanie_H]

ahhh....
Thanks for the info.
always good to learn from mistakes!