|
| 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
November 30th, 2009, 02:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Complicated text to date query - urgently need help please
HIya,
I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather 
|
|
#2
November 30th, 2009, 04:02 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
I would probably take this in steps.
Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather
|
|
#3
December 1st, 2009, 12:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
Hi John,
thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather .
|
|
#4
December 1st, 2009, 02:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
First Query
== Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather .
|
|
#5
December 1st, 2009, 02:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
First Query
== Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather .
|
|
#6
December 1st, 2009, 02:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
First Query
== Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather .
|
|
#7
December 1st, 2009, 04:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
Thanks John,
Finally got it all fixed - SUPERB! Cheers Heather "John Spencer" wrote: First Query == Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather . .
|
|
#8
December 1st, 2009, 04:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
Thanks John,
Finally got it all fixed - SUPERB! Cheers Heather "John Spencer" wrote: First Query == Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather . .
|
|
#9
December 1st, 2009, 04:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Complicated text to date query - urgently need help please
Thanks John,
Finally got it all fixed - SUPERB! Cheers Heather "John Spencer" wrote: First Query == Open a new query == Add your table == Add the new field and the old field == Change the old field to read RealDate: IsDate([OldField]) == Set the criteria under this calculated column to True == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate([OldField]) Second query == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#####" or [OldField] Like "######" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@")) Third query is basically a modification of the second query - changing the format to reflect the different structure of the dates. == Open a new query == Add your table == Add the new field and add the old field (twice) == Change the first old field to read RealDate: IsDate(Format([OldField],"@@/@@/@@@@") == Set the criteria under this calculated column to True == Set the criteria under the new field to Is not null == Set the criteria under the second old field to [OldField] Like "#######" or [OldField] Like "########" (Note: Access will probably restructure the criteria when you save the query) == Select Query: Update from the menu == Enter the following in UPDATE TO under the new field CDate(Format([OldField],"@@/@@/@@@@")) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: Hi John, thanks a lot for that. Please could you let me know how to do this using a non-SQL set of commands. I'm still new to Access and can just about figure out how to write updates in access using the usual query design view. Thanks very much Heather "John Spencer" wrote: I would probably take this in steps. Step 1 would be to add new field to the table to store the real date. First Update Query. UPDATE YourTable SET NewField = CDate([OldField]) WHERE ISDate([OldField]) That should take care of the formats 03/04/09 and 03/04/2009 and even 3/4/09 and 3/4/2009 Next update query - should handle 5 and 6 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@")) WHERE NewField is Not Null AND ([OldField] Like "#####" or [OldField] Like "######") AND ISDate(Format([OldField],"@@/@@/@@")) Next update query - should handle 7 and 8 number characters UPDATE YourTable SET NewField = CDate(Format([OldField],"@@/@@/@@@@")) WHERE NewField is Not Null AND ([OldField] Like "#######" or [OldField] Like "########") AND ISDate(Format([OldField],"@@/@@/@@")) You might need to handle the remainder manually or find some pattern you can use to update them with a query. You could use this to identify which records you need to update SELECT OldField, NewField FROM YourTable WHERE NewField is Null Once all that is done, you could then update the OldField with a formatted string based on NewField's content. I would not bother to do so since it is much easier to work with real dates instead of strings that look like dates. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Heather wrote: HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
