Solved

DateAdd Function ???

Posted on 2006-06-21
8
461 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 55 total points
ID: 16949933
format (dateadd("m",-6, Now()), "yyyymm")

Set the -6 to whatever you want.

Pete
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

733 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