?
Solved

do while macro

Posted on 2014-01-19
7
Medium Priority
?
314 Views
Last Modified: 2014-01-19
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
Comment
Question by:cwbarrett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
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:

 DateAdd("yyyy",1,[YourDateField])
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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.
;-)

JeffCoachman
Database55.mdb
0
 

Author Comment

by:cwbarrett
ID: 39793129
Worked great!  Thank you!
Charlie
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Closing Comment

by:cwbarrett
ID: 39793130
Solution was perfect.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39793144
Glad I could help!
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39793150
...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
 

Author Comment

by:cwbarrett
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.
Charlie
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 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