Year in Source Data Changed to Wrong Date in Table (1 Viewer)

Neighbour_Gal

New member
Local time
Today, 04:33
Joined
May 16, 2017
Messages
3
I am creating a database for the church where I work.

On my source spreadsheet, some of the baptism dates are complete: mm/dd/yyyy

but others only have the year: yyyy

When I imported the data, the Baptism Date field was imported witha data type of Date/Time. I just noticed that for the records where there was only a year given, there is now an incorrect date. For example, a baptism date of 1947 gives me a date of 04/30/1905.

I tried changing the data type of that field to text, but this did not fix the errors.

How would you go about solving this problem? Thankfully, I have only about 120 records and not all of those have problems. I could go through each one individually and fix them, but if there is a simpler fix, that would be nice.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:33
Joined
Sep 21, 2011
Messages
14,038
If you were to assume all those with only a year have the month of 1 and day of 1 you could try something like
dateserial(datefield,1,1) where datefield < 2017 or some such number ?
 

Neighbour_Gal

New member
Local time
Today, 04:33
Joined
May 16, 2017
Messages
3
If you were to assume all those with only a year have the month of 1 and day of 1 you could try something like
dateserial(datefield,1,1) where datefield < 2017 or some such number ?

I appreciate your speedy response, but I do not understand fully what you are suggesting that I do. I am very new to MS Access (still making my way through an online course). Where would I put this command?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:33
Joined
Sep 21, 2011
Messages
14,038
I would first of all make copy of your table.
Then I suggesting that you first create a query in the query designer to retrieve all records where the date is less than 2017.
That will tell you how many records are affected. If only a few do it by hand.
If too many then for the next field use the expression.

Code:
NewDate:DateSerial(cDbl(YourDateField),1,1)

Run that. That should show you two columns of data, the first will show the date you need to correct and the second will have the 1st of the 1st for the year.

Once you are happy with what is produced change the query to an update query and update the first field with that expression.

If you get it wrong, make a copy and start again.

So essentially make sure you have the records you want to amend.
Next make sure the data conversion is correct
Lastly make the changes.

HTH
 
Last edited:

Users who are viewing this thread

Top Bottom