Populate a table column with rearranged content from another column

[mlevel]

This SHOULD be doable with some kind of query but I cannot figure how or where to enter it.

I created a table many years ago with dates as text in mm/dd/yyyy format. I want dates in yyyy-mm-dd format. I added a newdate column that's empty.

Should be something like:
replace all newdate with substr(olddate, 5,4) + "-" + substr(olddate,1,2) + "-" + substr(olddate, 3,2)

I could've done this in a minute in FoxPro (I am old). I've tried to figure it out in Access for like 15 years now!

It's just a hobby database (authors and quotations). Where would I enter this command in Access and what's the correct syntax?

Eternal gratitude for an answer that works...

Steve M.

[Micron]

Since your dates are strings, one way is in query design:

NewDate:DatePart("yyyy",cdate(olddate)) & "-" & DatePart("m",cdate(olddate)) & "-" & DatePart("d",cdate(olddate))

[Micron]

But simpler, perhaps
format(datevalue(olddate),"yyyy-mm-dd")

[orange]

?? Why would you define a Date as a string/text ??

CDate(your textual Date) will return a Date.
But formating a date results in a string

Sample:

Sub testcDate()
    Dim X As String: X = "10/23/2016"
    Dim D As Date
    Dim Y As String
    D = CDate(X)
    Y = Format(D, "yyyy-mm-dd") 'this reult will still be a string
    Debug.Print Y
End Sub

Result: 2016-10-23

[Welshgasman]

But simpler, perhaps
format(datevalue(olddate),"yyyy-mm-dd")

Still a string though?

[Welshgasman]

This SHOULD be doable with some kind of query but I cannot figure how or where to enter it.

I created a table many years ago with dates as text in mm/dd/yyyy format. I want dates in yyyy-mm-dd format. I added a newdate column that's empty.

Should be something like:
replace all newdate with substr(olddate, 5,4) + "-" + substr(olddate,1,2) + "-" + substr(olddate, 3,2)

I could've done this in a minute in FoxPro (I am old). I've tried to figure it out in Access for like 15 years now!

It's just a hobby database (authors and quotations). Where would I enter this command in Access and what's the correct syntax?

Eternal gratitude for an answer that works...

Steve M.

Just make your dates actual dates, and then format them how you want, but leave them as actual dates.

[Micron]

OK, it's obvious that the original values are text, not that I think it's good. I chose to ignore that given that the values could have come from another source, such as a csv file. So yes, still text but it answers the question. If OP wants to know how to convert the strings to dates, then I imagine he/she will ask.

[mlevel]

Since your dates are strings, one way is in query design:

NewDate:DatePart("yyyy",cdate(olddate)) & "-" & DatePart("m",cdate(olddate)) & "-" & DatePart("d",cdate(olddate))

Thanks very much for the reply (the fields are text because it's a very old database, but also because we sometimes don't know exactly when a person was born or died, so I have incomplete dates like 07/12/192? that seem to be problematic in strict date format).

Apologies, but I really know nothing of tinkering in Access. So I would go to Query Design, select my People table that contains these two fields, and then... where do I go to enter this code?

[Micron]

At the very top of the query design grid. Enter in a new column. A date with a ? is kinda dumb. Date data type and no dates for the unknowns would have been better.

[mlevel]

Thanks again.

This works, kinda, but is also "kinda dumb" for my purposes. Also, it doesn't add the new column to the table?

I really want to keep the dates in string form because the table contains data about people. I need *approximate* dates sometimes where nobody knows the *exact* date a person was born, but I still want to be *close* and not throw away the part of the date that I *do* know.

[Micron]

Then change the select query to an update query if you already have added a table field. Or just overwrite the existing values in the original field? I'd copy the table first in case that goes bad.