Select 1st date of subsequent month based

[G2]

Hi,

I'm trying to build a calculated field in an Access query to do the
following:

Select the 1st date of the month following whatever month the "Rate
Effective Date" is in.
For example:

If Rate Effective Date = 7/9/2009, the field will return 8/1/2009.
If Rate Effective Date = 12/31/2009, the field will return 1/1/2010.

It's probably a simple line of sql code or easy to build with the
function builder, but I haven't worked in Access for awhile and google
has fallen short.

Thanks!!!

 

[Allen Browne]

Try:
DateAdd("m", 1, [Rate Effective Date] - Day([Rate Effective Date]) + 1)

Subtracting the day of the month yields the last day of the previous month.
Adding 1 is the first of the current month.
The DateAdd() then adds 1 month.

 

[Jerry Whittle]

Debug.Print DateAdd("m",1,#7/9/2009#)-Day(#7/9/2009#)+1 = 8/1/2009

In a query:

TheNextMonth: DateAdd("m",1,[Rate Effective Date])-Day([Rate Effective
Date])+1

 

[John W. Vinson]

Hi,

I'm trying to build a calculated field in an Access query to do the
following:

Select the 1st date of the month following whatever month the "Rate
Effective Date" is in.
For example:

If Rate Effective Date = 7/9/2009, the field will return 8/1/2009.
If Rate Effective Date = 12/31/2009, the field will return 1/1/2010.

It's probably a simple line of sql code or easy to build with the
function builder, but I haven't worked in Access for awhile and google
has fallen short.

Thanks!!!

DateSerial(Year([Rate Effective Date]), Month([Rate Effective Date]) + 1, 1)

will work (as will the DateAdd suggestions). Just in case you need the last
day of the current month instead of the first day of the next month, use 0
instead of 1 for the last argument.

 

[G2]

Excellent, thank you,that would do it!