How to calculate the first of the month following the end of previous month?

[yappy77]

I have a query that I need help in writing the expression to calculate the first of the month following the end of previous month.

Example:
Policy expiration date = between 2/1/11 and 2/28/11
Date Letter Sent = 3/1/11

This is what I have and it only returns the number 3:

Date Letter Sent: (DatePart("m",[General Liability Policy Expiration Date])+1)

The correct answer should be 3/1/11. Please help. What am I missing in the expression?

Your help will be greatly appreciated.

Thanks,
Yappy

[beacon]

Hi Yappy,

I think you would be better off using the DateAdd function instead of DatePart. With DateAdd you can just add one day to the expiration date and it will give you the next day. Assuming that the policy always expires on the last day of the month, you'll always get the first day of the next month.

Date Letter Sent: DateAdd("d", 1, ",[General Liability Policy Expiration Date])
 

Hope this helps and good luck,
beacon

[yappy77]

Thanks, Beacon! Unfortunately the last day of the month is not always the expiration date. Do you have another suggestion/solution?

Yappy

[beacon]

Ok, try this on for size:

Date Letter Sent: DateSerial(Year([General Liability Policy Expiration Date]),Month([General Liability Policy Expiration Date])+1,1)
 

[Rabbit]

Beacon, won't that fail on december? Or does DateSerial know to increment the year?

[beacon]

@Rabbit...that was supposed to be a fun surprise for Yappy. =)

[yappy77]

So, what is the answer? Will it fail on December? And, if so, how can it be fixed?

[beacon]

It won't fail on December, per se, but it won't increment the year to the next year. If you have an expiration date of 12/10/2011, the expression will return a result of 01/01/2011 instead of 01/01/2012

You'll need to add a conditional statement that tests the current month to determine whether or not it's December. If it is December (the True part of the statement), then you'll add one to the year. If it isn't December (the False part of the statement), then you'll use what I provided above.

[Rabbit]

Or you could DateSerial to the first of the current month and then DateAdd a month.

[beacon]

@Rabbit...is this what you had in mind?:

Date Letter Sent: DateAdd("m", 1, DateSerial(Year([General Liability Policy Expiration Date]),Month([General Liability Policy Expiration Date]),1))
 

I'm not sure if it works because I don't have time at the moment to test it out, but it looks like it would work on the surface.

[Rabbit]

That's pretty much what I was thinking.

[yappy77]

This works perfectly. Thank you so much for your help!

[yappy77]

Rabbit,
Your idea worked perfectly. Thank you for your help!

[gershwyn]

Actually, the DateSerial function will carry over to the next year if the you supply a month of 13.

Print DateSerial(2010, 13, 10)

gives:

1/10/2011

I'm sure date settings could come into it, but it works on my machine. Anyway, the accepted answer works just as well and as far as I know are equivalent, but I just wanted to point that out.

[beacon]

They are equivalent...the only difference being that you identify the literal 13th month and the other is adjusted based on the value in the field.