?
Solved

DateAdd Function ???

Posted on 2006-06-21
8
Medium Priority
?
471 Views
Last Modified: 2008-02-26
I have a date in the format yyyymm and I want to be able to take 1 month off.

IE: 200606 would become 200605 I could just use [month]-1 but that would only work for 11 months of the year as 200601 would become 200600 and I want it to be 200512.

I think I need the dateadd function but cant for the life of me figure it out.

Carl.
0
Comment
Question by:Carl2002
  • 3
  • 3
  • 2
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16949736
Hi Carl2002,

If you are storing the date in  a datetime field then you can use Dateadd() to subtract 1 month.


Pete
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16949748
The field is a result of a query the query is simply Format(now(),"yyyymm"). Will this be a string ?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16949825
Format(DateSerial(Left([Field], 4), Right([Field], 2), 0), "yyyymm")
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 4

Author Comment

by:Carl2002
ID: 16949845
This works to find the prior month but how would it then find 6 months ago ?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16949869
Carl2002,
You said you wanted to take 1 month off; where does 6 months come into it?

Pete
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16949884
OK sorry guys think my initial question didn't explain it all. I do want to take 1 month off, but there will be times I want to take more than 1 month off. Is this possible?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16949916
Another way to take 1 Month off...
Format(DateSerial(Left([Field], 4), Right([Field], 2)-1,1), "yyyymm")

To Take 6 months off...
Format(DateSerial(Left([Field], 4), Right([Field], 2)-6, 1), "yyyymm")

To Add 6 Months...
Format(DateSerial(Left([Field], 4), Right([Field], 2)+6, 1), "yyyymm")
0
 
LVL 77

Accepted Solution

by:
peter57r earned 220 total points
ID: 16949933
format (dateadd("m",-6, Now()), "yyyymm")

Set the -6 to whatever you want.

Pete
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

750 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