Changing portions of a date field

[Jerry Crosby]

Not sure whether I should post this in the Query newsgroup, or the report
newsgroup, so I'll try it here.

I need the code that takes a given date (ex: "9/30/05") and creates a new
date that is the first of the next month (in this case, it would be
"10/1/05").

Of course, to complicate matters, there is the issue of December dates,
which need to be changed to January 1 of the following year.

Ideas?

Thanks in advance.

Jerry

 

[Baz]

Not sure whether I should post this in the Query newsgroup, or the report
newsgroup, so I'll try it here.

I need the code that takes a given date (ex: "9/30/05") and creates a new
date that is the first of the next month (in this case, it would be
"10/1/05").

Of course, to complicate matters, there is the issue of December dates,
which need to be changed to January 1 of the following year.

Ideas?

Thanks in advance.

Jerry

DateAdd("m",1,DateSerial(Year(some_date),Month(some_date),1))

 

[OfficeDev18 via AccessMonster.com]

While you're getting complicated, you could also do:

CDate(Format(DateAdd("m",1,Date()), "mm/1/yy"))

Not sure whether I should post this in the Query newsgroup, or the report
newsgroup, so I'll try it here.

[quoted text clipped - 11 lines]

Jerry

DateAdd("m",1,DateSerial(Year(some_date),Month(some_date),1))

 

[Jerry Crosby]

Thanks. That gave me the code, now I'm trying to solve the situation when
two dates span a year. The following query isn't giving me an error, but
rather than change the date, I get a "-1".

The logic behind it is this: If the year of the DepartureDate field is
greater than the year of the ArrivalDate field (ex: arrives in 2005 and
departs in 2006), then create a new field (NewArrival) that takes the
ArrivalDate and adds 1 to the year, 1 to the month, and sets the day to 1.

So, if ArrivalDate = 12/24/05 and DepartureDate = 1/5/06, then
NewArrival = 1/1/06

Here's what I've written:

NewArrival:
IIf(DatePart("yyyy",[DepartureDate])>DatePart("yyyy",[ArrivalDate]),DateAdd("m",1,DateSerial(Year([ArrivalDate]),Month([ArrivalDate]),1)))
And
DateAdd("yyyy",1,DateSerial(Year([ArrivalDate]),Month([ArrivalDate]),Day([ArrivalDate])))

I'm not that well versed in nested If statements and I'm not sure if I've
used the "and" properly, but at least I'm not getting an error, just wrong
results!

Hope you can help.

Jerry

 

[John W. Vinson/MVP]

Not sure whether I should post this in the Query newsgroup, or the report
newsgroup, so I'll try it here.

I need the code that takes a given date (ex: "9/30/05") and creates a new
date that is the first of the next month (in this case, it would be
"10/1/05").

Even simpler: DateSerial(Year([datefield]), Month([datefield] + 1, 1)

The DateSerial function is clever enough to realize that the thirteenth
month of 2005 is January, 2006.

John W. Vinson/MVP

 

[Jerry Crosby]

No need to reply to this, John's suggestion of how to use the DateSerial
field helped me solve the matter.

Thanks to all.

Jerry

 

[Baz]

While you're getting complicated, you could also do:

CDate(Format(DateAdd("m",1,Date()), "mm/1/yy"))

--
Sam


Message posted via AccessMonster.com

Doing it that way is region-dependent. Here in the UK, on 1st October, it
gives me 11th January, not 1st November.

If you've got a simpler way of doing it than I gave, let's hear it.

 

[Baz]

Not sure whether I should post this in the Query newsgroup, or the report
newsgroup, so I'll try it here.

I need the code that takes a given date (ex: "9/30/05") and creates a new
date that is the first of the next month (in this case, it would be
"10/1/05").

Even simpler: DateSerial(Year([datefield]), Month([datefield] + 1, 1)

The DateSerial function is clever enough to realize that the thirteenth
month of 2005 is January, 2006.

John W. Vinson/MVP

Doh! I never knew that, thanks.

BTW, you're missing a closing parenthesis.