Creating 1 date from 2 date fields

D

dtoney

I have a project in which I am creating a retiree DB from multiple sources.
My PR source has bad data in the yyyy field (shows erroneous year!) The
other source (we'll call it OS )pads in the day with a 15 (thus the erroneous
day!). Is there a way to match on the key field and create one GOOD date? I
would like to take the date from the PR field and the year from the OS, if
possible.
 
J

Jeff L

DatePart("m", [PRDateField]) & "/" & DatePart("d", [PRDateField]) & "/"
& DatePart("yyyy", [OSDateField])

If you are using this in a field on a report or form, you will need an
= at the beginning. Hope that helps!
 
D

dtoney

sure... employee# 10112 on PR source shows a DOB of 1/10/2029 whereas on OS
source shows 1/15/1929 whereas the actual DOB would be 1/10/1929. PR has the
wrong yyyy & OS has a wrong dd... so I need the month &day from the PR record
& the yyyy from the OS.
 
J

Jeff L

You can use it however you want. You can use it in an append, update,
or simple select. It all depends on what you want to do with it.
 
D

dtoney

I would like to create a valid date field somewhere... thought about a new
talble with just the empID & actualDOB... whatever is best for the long haul.
What do you suggest?
 
D

dtoney

or... what if I use the date from the OS & do an update on the dd from the
PR? Wouldn't that be an update query where I would just need the
DatePart("d",[tblPRdata]![DOB]) part of the expression?
Only problem is when I do this... I get zero records updated!
Please help!!
 
J

John Spencer

SQL would look something like the following.

SELECT OS.KeyField, PR.KeyField, OS.DateField, Pr.DateField,
DateSerial(Year(OS.DateField),Month(PR.DateField),Day(PR.DateField)) as
GoodDate
FROM OS INNER JOIN PR
ON OS.KeyField = PR.Keyfield
WHERE OS.DateField is not null and PR.DateField is not null


You should be able to change that to an update query if you have a field to
store the results in.
 
J

Jeff L

I would suggest that you update your existing table with the correct
information. Having a separate table with just ID and DOB is kinda
silly. When you do your update, you will need to use the entire
statement I gave you earlier.
 
D

dtoney

I figured it out... I made a select query & pulled the PR EmpID & DOB & on
the criteria line inserted the DatePart("m",[tblPRdata]![DOB]) & "/" &
DatePart("d",[tblPRdata]![DOB]) & "/" & DatePart("yyyy",[tblOSData]![DOB])
which gives me the correct information from both fields! whew!!
 
Top