Adding 6 months to date.

[tgraves8]

I have a pretty basic access database. What I am looking to do is take a date that looks like "2007-11". I need to be able to add 6 months to all the dates so that it will show as "2008-05",

Any assistance with this would be greatly appreciated.

 

[lespaul]

Is it a "real" date field or is it a text field that "looks" like a date?

Leslie

 

[PWise]

format(dateadd("m",6,DateValue("2007-11" & "-" & 1 ) ) ,"yyyy-mm")

 

[tgraves8]

That worked for one entry, I apologize for the delay in writing back. What I have is a many different date fields.

Like 2007-09, 2007-10, etc.. on until the end of 2008

format(dateadd("m",6,DateValue("2007-11" & "-" & 1 ) ) ,"yyyy-mm")

I tried doing it in excel and access and I am not having any luck changing just the field with the date in it.

 

[PHV]

format(dateadd("m",6,DateValue([!][your date field][/!] & "-" & 1 ) ) ,"yyyy-mm")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

This seems even more confusing. Are your date fields named "2007-09" and "2007-10"?

Can you provide some actual table and field names, field types, and sample data? Then show what the new values should be. Do you expect to only display the calculated value or do you want to store it some place?

Duane
Hook'D on Access
MS Access MVP

 

[tgraves8]

I have a table that has a list of computers and in the list has a date of when the warrarnty expires. In that Date field it has year-month (2007-12, 2008-03, and any other year-month combination)

I have tired to do this in excel with out any luck either.

If i use this in access it works. But I am not only listing that year month.

format(dateadd("m",6,DateValue("2007-11" & "-" & 1 ) ) ,"yyyy-mm")

So I assume that in the query for the criteria is where i put this.

If there is a way to do this in excel that would be fine with me as well. I am running in to the same problem there.

So (in excel) I have a column with the dates and in the next column I try using a similar code but can't get it to work on just the previous field to add 6 months.

 

[lespaul]

So your table looks like:
[tt]
ComputerID WarrantyDateExpires
1 2007-11
2 2007-12
[/tt]

and you want to add 6 months to the WarrantyDateExpires field so you end up with:

[tt]
ComputerID WarrantyDateExpires SixMonthsLater
1 2007-11 2008-05
2 2007-12 2008-06
[/tt]

SELECT ComputerID, WarrantyDateExpires, format(dateadd("m", 6, ([b]WarrantyDateExpires[/b] & "-" & 1)), "yyyy-mm") From [b]TableName[/b]

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

Date field it has year-month

A date field always stores a full date, not just a year-month. You might have a form or report display only the year-month.

If the field is a date field, just use
[tt][blue] DateAdd("m",6,[YourFieldName])[/blue][/tt]
to add six months. Then use the Format property of your text box to display only the Year and Month.

Can you reply to my question "Do you expect to only display the calculated value or do you want to store it some place?"

Duane
Hook'D on Access
MS Access MVP