Change date from 15th to 1st of next mth 1

[Zonie32]

I have a field in my query called CaseEffDate. Some effective dates are the 15th of the mth, ie 1/15/2004. I want to have all dates that show the 15th to become a NewCaseEffDate as the 1st day of the following month.

Example:

I want 1/15/2004 to become 2/1/2004.
I want 10/15/2003 to become 11/1/2003.

If the date is anything other than the 15th then I want to leave AS IS. Any help is appreciated.

Using WinXP, Access 2003.

 

[PHV]

NewCaseEffDate: IIf(Day(CaseEffDate)=15, DateSerial(Year(CaseEffDate),1+Month(CaseEffDate),1), CaseEffDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zonie32]

This works great PHV! Thanks a lot.

Would you mind if I asked for help on one more thing...

I have a field called RateGuarantee which shows either 6 or 12. I would like to now take this NewCaseEffDt and add either 6 or 12 months to it and get that date, which I'll call it RGExpiredDate.

So if NewCaseEffDt = 1/1/2004 and the RateGuarantee shows 12, then I want the new field RGExpiredDate to be 1/1/2005. I want it to add either 6mths or 12mths to it.

thanks again. I really appreciate your help!

 

[PHV]

RGExpiredDate: DateAdd('m', RateGuarantee, IIf(Day(CaseEffDate)=15, DateSerial(Year(CaseEffDate),1+Month(CaseEffDate),1), CaseEffDate))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zonie32]

thank you. it works just fine!