If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delimiting Field Containing - Character
I need to turn the following types of number strings into a date
field: 03-1-12345 98-12-12345 Where, the strings would represent: January 2003 December 1998 In Excel, I could just use the function Text to Columns, and delimit on the character "-" and then put the string back together. Unfortunately, I am accessing this table using ODBC, and the values for this field are constantly updated. Any help would be most appreciated. |
#2
|
|||
|
|||
Delimiting Field Containing - Character
DateSerial(Val("03-1-12345"),Val(Mid("03-1-12345",Instr(1,"03-1-12345","-")+1)),1)
will return January 1, 2003 DateSerial(Val("98-12-12345"),Val(Mid("98-12-12345",Instr(1,"98-12-12345","-")+1)),1) will return December 1, 1998 So subsititute your field name for the strings above in the formula. Once you have a real date, you can choose to display it anyway you want. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. wrote in message oups.com... I need to turn the following types of number strings into a date field: 03-1-12345 98-12-12345 Where, the strings would represent: January 2003 December 1998 In Excel, I could just use the function Text to Columns, and delimit on the character "-" and then put the string back together. Unfortunately, I am accessing this table using ODBC, and the values for this field are constantly updated. Any help would be most appreciated. |
Thread Tools | |
Display Modes | |
|
|