Parcing Year and Month from a string

[brhouser]

I have a field, PERIOD_ID, that is in a text format, I am trying to convert this into an actual date format:

an example of what the data looks like in this field is 0208 this correspondes to August 2002.

I have tried the format function by doing this:

format((mid([period_id],3,2) & 20 & left([period_id],2), "mm/yyyy&quot

I would expect this to return 08/2002, however the dates in the return are way off from the original data in the PERIOD_ID field.

For example, the 0208 returns 07/2124 as the date.

I have tried doing this in 2 separate steps:

format(mid([period_id],3,2),"mm&quot
format(20&left([period_id],2),"yy&quot

And get the same results - 07 for MM and 2124 for YYYY (for the 0208 period_id).

I have also tried to convert these into regular integers, then convert into dates, then concatinate them together.

Nothing is working so far, any help would be grateful!

 

[lameid]

You first have to convert the data to a valid date to use format. Cdate works nicely...

Format(Cdate(Right([period_id],2) & "/1/" & left([period_id],2)),"mm/yyyy&quot

I also used right instead of mid because it should be faster but probably not noticible.

 

[PaulBricker]

Try this to start.
Mid([period_id],3,2) & "/20" & left([period_id],2)

If that gives you what you want and you want the datatype to actually be Date then try this.

CDate(Mid([period_id],3,2) & "/20" & left([period_id],2))

This will return 8/1/2002 and that may not be what you want.

Format(Mid("0208",3,2) & "/20" & left("0208",2), "mm/yyyy&quot

returns 08/2002 but it will not be a date format, it will be text.


Paul

 

[brhouser]

Thanks lameid and Paul - both worked, and I have what I want now.

Thanks again!

Brian

 

[brhouser]

Now, any suggestions to how to make the day in the date always be the last day of the month?

 

[lameid]

Add a month to the first day of the month and then subtract a day...

DateAdd("d",-1,Dateadd("m",1,Cdate(Right([period_id],2) & "/1/" & left([period_id],2))))