Convert mid month date to month end date

[dallin]

I'm reading in a text file that contains a date paid field that can be any
day of the month -- I want to convert that date to the end of the month date:
for example

record 1, date paid 05/12/2009, mthend 05/31/2009
record 2, date paid 05/19/2009, mthend 05/31/2009
record 3, date paid 06/02/2009, mthend 06/30/2009

Is there a function in Access that will do this for me?

 

[fredg]

I'm reading in a text file that contains a date paid field that can be any
day of the month -- I want to convert that date to the end of the month date:
for example

record 1, date paid 05/12/2009, mthend 05/31/2009
record 2, date paid 05/19/2009, mthend 05/31/2009
record 3, date paid 06/02/2009, mthend 06/30/2009

Is there a function in Access that will do this for me?

This is a Text datatype field? All the values are mm/dd/yyyy ?
= DateSerial(Right([FieldName],4),Left([FieldName],2) + 1,0)

The day 0 of any month is the last day of the previous month. Make
sense?

 

[Marshall Barton]

I'm reading in a text file that contains a date paid field that can be any
day of the month -- I want to convert that date to the end of the month date:
for example

record 1, date paid 05/12/2009, mthend 05/31/2009
record 2, date paid 05/19/2009, mthend 05/31/2009
record 3, date paid 06/02/2009, mthend 06/30/2009

Is there a function in Access that will do this for me?

DateSerial(Year(datepaid), Month(datepaid) + 1, 0)

 

[dallin]

No, this is a date field I'm reading in. Does that change the formula?

I'm reading in a text file that contains a date paid field that can be any
day of the month -- I want to convert that date to the end of the month date:
for example

record 1, date paid 05/12/2009, mthend 05/31/2009
record 2, date paid 05/19/2009, mthend 05/31/2009
record 3, date paid 06/02/2009, mthend 06/30/2009

Is there a function in Access that will do this for me?

This is a Text datatype field? All the values are mm/dd/yyyy ?
= DateSerial(Right([FieldName],4),Left([FieldName],2) + 1,0)

The day 0 of any month is the last day of the previous month. Make
sense?