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

DateAdd Function ???

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
Carl2002
Asked:
Carl2002
  • 3
  • 3
  • 2
1 Solution
 
peter57rCommented:
Hi Carl2002,

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


Pete
0
 
Carl2002Author Commented:
The field is a result of a query the query is simply Format(now(),"yyyymm"). Will this be a string ?
0
 
Rick_RickardsCommented:
Format(DateSerial(Left([Field], 4), Right([Field], 2), 0), "yyyymm")
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Carl2002Author Commented:
This works to find the prior month but how would it then find 6 months ago ?
0
 
peter57rCommented:
Carl2002,
You said you wanted to take 1 month off; where does 6 months come into it?

Pete
0
 
Carl2002Author Commented:
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
 
Rick_RickardsCommented:
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
 
peter57rCommented:
format (dateadd("m",-6, Now()), "yyyymm")

Set the -6 to whatever you want.

Pete
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: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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