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
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 said:
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
.
.
