How do I display the last day of a month in an update query?

[Guest]

I want to create an update query that given a specific date in one field,
e.g. 2/14/04, will update another field to the last day of that month, in
this case to 2/29/2004; or if given 5/29/04 will update to 5/31/04, and so
forth. Any ideas out there?

Thanks

 

[Rick B]

DateSerial(Year([SomeDateField]), Month([SomeDateField]), 1)

 

[Guest]

Thank you so much! have a great weekend!

DateSerial(Year([SomeDateField]), Month([SomeDateField]), 1)

I want to create an update query that given a specific date in one field,
e.g. 2/14/04, will update another field to the last day of that month, in
this case to 2/29/2004; or if given 5/29/04 will update to 5/31/04, and so
forth. Any ideas out there?

Thanks

 

[fredg]

I want to create an update query that given a specific date in one field,
e.g. 2/14/04, will update another field to the last day of that month, in
this case to 2/29/2004; or if given 5/29/04 will update to 5/31/04, and so
forth. Any ideas out there?

Thanks

The last day of a given month is day 0 of the following month.

EndMonthateSerial(Year([DateField]), Month([DateField])+1, 0)

 

[Rick B]

Fred is right! I pasted the formula for the FIRST day of the month.
SORRY!!!

Rick B

I want to create an update query that given a specific date in one field,
e.g. 2/14/04, will update another field to the last day of that month, in
this case to 2/29/2004; or if given 5/29/04 will update to 5/31/04, and so
forth. Any ideas out there?

Thanks

The last day of a given month is day 0 of the following month.

EndMonthateSerial(Year([DateField]), Month([DateField])+1, 0)