Select 1st date of subsequent month based

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!!!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"G2" wrote in message
...
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!!!

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
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"G2" wrote:

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!!!

On Thu, 9 Jul 2009 08:38:10 -0700 (PDT), G2 wrote:

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.

--

John W. Vinson [MVP]

Excellent, thank you,that would do it!