Intergrating colums in an Access Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I am working in a massive table in Access and notice that my date is split
into 3 different colums, how would I integrate the three values in order to
have the date in a single column?
 
I'm assuming that the year, month, and day are stored separately ...

UPDATE tblTest SET tblTest.TestDate =
DateSerial([TestYear],[TestMonth],[TestDay]);

In my test, the columns 'TestYear', 'TestMonth' and 'TestDay' were numeric
columns. If in your table they are text columns, you may need to convert
their values as follows, but I have not tested this ...

UPDATE tblTest SET tblTest.TestDate =
DateSerial(CInt([TestYear]),CInt([TestMonth]),CInt([TestDay]));

A further complication may arise if any of those columns contain Null values
or other values that can not be converted to integers in the appropriate
ranges, so you may need to add appropriate criteria to the query. For
example the following query will not attempt to update any records with Null
values in any of the three columns ...

UPDATE tblTest SET tblTest.TestDate =
DateSerial([TestYear],[TestMonth],[TestDay])
WHERE (((tblTest.TestDate) Is Not Null) AND ((tblTest.TestYear) Is Not Null)
AND ((tblTest.TestMonth) Is Not Null));
 
Jenn said:
If I am working in a massive table in Access and notice that my date
is split into 3 different colums, how would I integrate the three
values in order to have the date in a single column?
Click to expand...

It looks like someone used three fields to keep the day, month and year
separate.

First take a look and find out what the field type(s) are for those
three fields. Next let us know if you will ever do any calculations on
that field, like adding three days or comparing which came first 1/5/89 or
2/15/02

I would suspect to find other design problems with the table(s) based on
this. I would guess they are not properly normalized as well. Do you see a
lot of repeated data in the table, like line after line of "Acme Co." in a
employer field?
 
They did use 3 fields to keep the month, day year. I am working with fixed
assets and noticed that the acquisition date of the assets was split in 3
different colums. For example:

Asset Description Month Day Year
1000002 Van 06 23 2004

It is a text field and I will not be doing any calculations in that field.
 
Back
Top