Interleaf data into one field

[cft]

I have a table that is comprised of 2 previously seperate tables. The data sources were different for each, so the formatting of the date fields (specifically) were different. One source parsed the date fields into 3 seperate text fields for YYYY, one field for MM, and one for DD. The other source provided the dates as MM/DD/YYYY.
I have parsed the date elements from the MM/DD/YYYY into seperate fields, but now do not know how to move the seperated fields into the fields from the first data source table.
In other words, I now have a table with 3 date sets. One from the original table that are seperated, and one with the whole date and another with its parsed components.
How can I move the parsed date fields into the blank fields in the original table?
The table looks something like this:
DOB-M DOB-D DOB-Y DOB-Full....Parsed MM Parsed DD and Parsed YYYY
03 12 1951
02/27/1941 02 27 1941
I need to move the data from the right fields to the null fields under the date format on the left side so it all ends up in the same columns.
Thanks in advance for any advice.

 

[DocNice]

This is a pretty sloppy way to do it, so you may want to wait for someone else to chime in, but I would export to excel and perform some manipulations on the data, such as replacing spaces with slashes.

 

[raskew]

Hi -

For starters you might want to read-up on how Access stores date/time http://support.microsoft.com/kb/130514/EN-US/.

Rather than playing-around trying to format text dates, suggest you first convert your text dates to date/time datatype. Once done, you can format them however you wish, but the underlying stored date will remain the same and you'll have access to all of the built-in date/time functions available to help manipulate your dates.

1) Add a new field (MyDate) to your table, ensuring you specify Date/Time data type.

2) Back-up your table!

3) Copy/paste this query-SQL to a new query.

UPDATE tblDateTest SET tblDateTest.MyDate = IIf(IsNull([tblDateTest].[TextDate]),DateSerial([yeardate],[monthdate],[daydate]),CDate([textDate]));

4) In SQL mode, modify the query to incorporate your table and field names.

The intent here is to populate [MyDate] with either:
a) The date/time (cDate()) equivalent of field [TextDate]
---or
b) The DateSerial() equivalent of the [yeardate], [monthdate], [daydate] fields.

Provided it functions successfully, you'll now have a single field ([MyDate]) properly structured in date/time data format. Once you've determined to your satisfaction that all is well, you may then (after having made a backup) delete the TextDate, YearDate, MonthDate, DayDate fields. Rename field MyDate to something more descriptive (don't name it Date) and you'll be off and running.

Please post back with your results.

Best wishes,

Bob