How to extract date from a datetime text field?

[stalk]

Dim Date_Tested As Date
Dim DateTime_Tested As Date
rundate= "2016-11-18 01:53:37 AM EST"

Date_Tested = DateValue(Mid([rundate], InStr([rundate], ",") - 1))
DateTime_Tested = CVDate(Mid([rundate], InStr([rundate], ",") + 1))



I need Date_Tested in the following format: 18-11-2016
And datetime_Tested in 18-11-2016 01:53:37 AM EST

Both in date time format.

Any suggestions?
Thank you

[JoeM]

Formatting is just how the data is presented, not how it is stored.
Dates/times in Access are actually stored as numbers, specifically the number of days since 1/1/1900, where time is a fractional component of one day.

So if you had a value that has both a date and a time component to it, you can drop the time part (the decimal part) by using the INT function.

Note that if you apply a Format to a date field in a calculation or in VBA using the FORMAT function, the result returned will actually be Text/String, which may present problems if you need to use this value in any sort of calculations. If you do, it is better to apply the formatting on the interface end, and not directly to the calculation.

If "rundate" is a text value like:
rundate= "2016-11-18 01:53:37 AM EST"
You can get just the date part of it like this:
Date_Tested = DateValue(Left([rundate], InStr([rundate], " ") - 1))

To return a text value like "18-11-2016", make the following changes:

    Dim Date_Tested As String
    rundate = "2016-11-18 01:53:37 AM EST"

    Date_Tested = Format(DateValue(Left([rundate], InStr([rundate], " ") - 1)), "dd-mm-yyyy")
    
    MsgBox Date_Tested

[Micron]

I'm easily confused. Why are you looking for a comma in rundate: InStr([rundate], ",") when there isn't one in the posted example?
(variables should not be enclosed in brackets)
This will evaluate to zero, which will make the Mid function become Mid(rundate,0,-1) which should generate an 'invalid procedure call' error.
Since rundate is not declared, I presume you are not using Option Explicit in all or at least this one module.
While I think the example given will work, the Instr function will still return 0, no? (because there are no spaces either)

[stalk]

Thank you for your help.

code is working if the date_tested is declared as string. But I want to store the date_tested as date value in the table so it will be easy to query.
What function should I use to convert this to date values..
Date_Tested = Format(DateValue(Left([rundate], InStr([rundate], " ") - 1)), "dd-mm-yyyy")

[JoeM]

Just remove the Format function - that coerces the entry to Text. You need to leave it as a Date.

Date_Tested = DateValue(Left([rundate], InStr([rundate], " ") - 1))

If you want it presented differently, you can change the Format property on that particular field in your table.

[stalk]

Thank you that worked