I'm trying to simply convert a text field into a datetime value in MS Access 365. This should be simple. However I don't seem to have access to functions like Date(), DateValue().

I want something where I can just pass some text in and it converts to a datetime type. My data looks like this:

I used the formula: I used this formula to get this: Left$([DateText],InStr(1,[DateText]," - ")) to remove the time from the DateText field.

Now I want to add a new field which is a date/time type using the data in the DateTextWithTimeRemoved field.

The table design view (also showing the conversion functions available) is like this:

And the date functions are these:

And so, from years ago I remember using functions like CDate() or Date() or DateValue() or what have you, but they're just not there. Do I have to install something to get these functions?

CodePudding user response：

Assuming you want to keep the same Table fields shown in your question:

1. Make a backup of your current table.

2. Change the Data Type of the DateThatIsAnActualDate field to Date/Time and DateTextWithTimeRemoved to Short Text.

3. Create an an Update Query. (Create -> Query Design -> select Update under Query Type.)

4. Add your Table (members)

    Field: DateTextWithTimeRemoved
    Table: members
    Update To: 
   

Left([DateText],InStr(1,[DateText]," -"))

next column

    Field: DateThatIsAnActualDate
    Table: members
    Update To: 

Format(DateValue(Left([DateText],(InStr(1,[DateText]," -")))),"dd/mm/yyyy")

5. Click Run !

6. Click Yes to confirm.

7. All done. Check the records in the members table.

CodePudding user response：

First, forget about the calculated fields. Use queries, that's what they are for. Tables are for data.

Second, the Expression Builder is hopeless. I have never found it useful.

Next, all you need for an easy conversion to true date values, is to remove the dash.

TextDate = "10 Dec 2020 - 16:52"

? Replace(TextDate, "-", "")
10 Dec 2020  16:52

This can be converted like this:

' Date only:
? DateValue(Replace(TextDate, "-", ""))
2020-12-10

' Date and time:
? CDate(Replace(TextDate, "-", ""))
2020-12-10 16:52

Now, your query could look this this:

Select
    ID,
    Username,
    DateValue(Replace(TextDate, "-", "")) As TrueDate,
    CDate(Replace(TextDate, "-", "")) As TrueDateTime
From
    Members

Note, that date/time values carry no format. Apply this for display only, so adjust the Format property where needed (the forms/reports where you view the data), if the default format (that of your Windows settings) doesn't fit.

Format: dd/mm/yyyy

The default is:

Format: Short Date