Obtain Month & Year from a Text formatted Date field using Left Fu

[reddy]

I have an Access table (1.5 million records) that has the Process.date
formatted as Text. It is not possible to change the datatype to date/time
format.
It looks like this: 2008-06-01 00:00:00.000

I am trying to obtain the Year and Month from this field using Text
Functions:
Please advise if there is a more efficient way:

Year:
IIf(Left(Table1!ProcessDate,4)="2006","2006",IIf(Left(Table1!ProcessDate,4)="2007","2007",IIf(Left(Table1!ProcessDate,4)="2008","2008","XXXX")))

Month:
IIf(Left(Right(Table1!ProcessDate,18),2)="01","01",
IIf(Left(Right(Table1!ProcessDate,18),2)="02","02",
IIf(Left(Right(Table1!ProcessDate,18),2)="03","03",
IIf(Left(Right(Table1!ProcessDate,18),2)="04","04",
IIf(Left(Right(Table1!ProcessDate,18),2)="05","05",
IIf(Left(Right(Table1!ProcessDate,18),2)="06","06",
IIf(Left(Right(Table1!ProcessDate,18),2)="07","07",
IIf(Left(Right(Table1!ProcessDate,18),2)="08","08",
IIf(Left(Right(Table1!ProcessDate,18),2)="09","09",
IIf(Left(Right(Table1!ProcessDate,18),2)="10","10",
IIf(Left(Right(Table1!ProcessDate,18),2)="11","11",
IIf(Left(Right(Table1!ProcessDate,18),2)="12","12"))))))))))))

 

[KARL DEWEY]

This will convert to a date --
DateSerial(Left([YourField],4),Mid([YourField],6,2),Mid([YourField],9),2))

If all you want is year and month then these ---
Year: Left([YourField],4)
Month: Mid([YourField],6,2)

 

[fredg]

I have an Access table (1.5 million records) that has the Process.date
formatted as Text. It is not possible to change the datatype to date/time
format.
It looks like this: 2008-06-01 00:00:00.000

I am trying to obtain the Year and Month from this field using Text
Functions:
Please advise if there is a more efficient way:

Year:
IIf(Left(Table1!ProcessDate,4)="2006","2006",IIf(Left(Table1!ProcessDate,4)="2007","2007",IIf(Left(Table1!ProcessDate,4)="2008","2008","XXXX")))

Month:
IIf(Left(Right(Table1!ProcessDate,18),2)="01","01",
IIf(Left(Right(Table1!ProcessDate,18),2)="02","02",
IIf(Left(Right(Table1!ProcessDate,18),2)="03","03",
IIf(Left(Right(Table1!ProcessDate,18),2)="04","04",
IIf(Left(Right(Table1!ProcessDate,18),2)="05","05",
IIf(Left(Right(Table1!ProcessDate,18),2)="06","06",
IIf(Left(Right(Table1!ProcessDate,18),2)="07","07",
IIf(Left(Right(Table1!ProcessDate,18),2)="08","08",
IIf(Left(Right(Table1!ProcessDate,18),2)="09","09",
IIf(Left(Right(Table1!ProcessDate,18),2)="10","10",
IIf(Left(Right(Table1!ProcessDate,18),2)="11","11",
IIf(Left(Right(Table1!ProcessDate,18),2)="12","12"))))))))))))

"2008-06-01"
This part of the data represents June 1, 2008?
All records are in exactly the same format of
4 digit year - 2 digit month - 2 digit day?

Add 3 new columns to the query grid.

TheYear:Left(Process.[date],4)
TheMonth:Mid(Process.[date],6,2)
TheDay:Mid(Process.[date],9,2)

Note: Date is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[raskew via AccessMonster.com]

Hi -

From the immediate (debug) window:

x = "2008-06-01 00:00:00.000"
y = datevalue(left(x, instr(x, " ")-1))
? y
6/1/2008
? format(y, "yyyy mm")
2008 06

HTH - Bob