Convert mid month date to month end date


D

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?
 
Ad

Advertisements

F

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?
 
M

Marshall Barton

dallin said:
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)
 
Ad

Advertisements

D

dallin

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

fredg said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top