do while macro

Posted on 2014-01-19
Last Modified: 2014-01-19

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.
Question by:cwbarrett
  • 4
  • 3
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39792747
Not sure of you requirements here or your usage, ...but you can easily add a year to a data with something like this:

LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 39792763
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.


Author Comment

ID: 39793129
Worked great!  Thank you!
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Closing Comment

ID: 39793130
Solution was perfect.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39793144
Glad I could help!

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39793150
...and you can see here for more info on the DateAdd function here:

Author Comment

ID: 39793345
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.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum Multiple Columns in Access Query 5 53
ms/access hyperlink/ftp 7 39
How do I refer to a session variable in a query? 4 24
Acc 2010 pause execution 10 13
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now