Creating 1 date from 2 date fields

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

Guest

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.
 
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!
 
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.
 
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.
 
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?
 
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!!
 
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.
 
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.
 
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!!
 
Back
Top