• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

do while macro

Hi,

I have a query with a set of records having a date field that I need to increment by 12 months leaving the day the same.  All records in the query need to have the date advanced 12 months so some sort of Do While not end-of-file would seem reasonable.  This needs to be done on a regular basis so I'll be placing a button in a form that runs a macro (preferred) or VBA or combo of both.  Is there a way to do this using macros only?  I prefer macros but I'll do whatever it takes to get this job done.

Help is appreciated.
Charlie
0
cwbarrett
Asked:
cwbarrett
  • 4
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Not sure of you requirements here or your usage, ...but you can easily add a year to a data with something like this:

 DateAdd("yyyy",1,[YourDateField])
0
 
Jeffrey CoachmanMIS LiasonCommented:
If you just need to "see" the next year, you can do this in a query:

SELECT ID, YourDateField,DateAdd("yyyy",1,[YourDateField]) AS AddYear
FROM YourTable.

If you want to actually "Change" the stored date, you can run a query like this:
    UPDATE YourTable SET YourTable.YourDateField = DateAdd("yyyy",1,[Yourdatefield]);
You can run this query directly:
Docmd.openquery "YourUpdateQueryName"

Or you can use code:
Currentdb.execute "UPDATE YourTable SET YourTable.YourDateField = DateAdd("yyyy",1,[Yourdatefield]);",dbfailonerror

Sample of both techniques attached, ...have fun.
;-)

JeffCoachman
Database55.mdb
0
 
cwbarrettAuthor Commented:
Worked great!  Thank you!
Charlie
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
cwbarrettAuthor Commented:
Solution was perfect.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help!
;-)

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
...and you can see here for more info on the DateAdd function here:
http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx
0
 
cwbarrettAuthor Commented:
Thanks, good info.  I have an old dbase (foxpro-DOS) app that I am trying to migrate to Access 2010.  I know what I need it to do, getting Access 2010 to do it sometimes proves difficult for me.  This web site has helped me for sure.
Charlie
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now