convert a date into a dateTime value

[JeroenMioch]

Hey guys,

I have a column in a table that holds dates.


I want to add a timevalue (00:00:00 would be fine) to this field.

Of course i made the field a standard DateTime field, but this does not add a timevalue.

Anyone know how ?

Greetings, Jeroen

[alansidman]

What happens when you change the characteristic of the field in your table design. Do it in a copy of your db to ensure that you don't disturb your actual db until you are sure it will work.

[CJ_London]

date, time and datetime are all the same thing - a special form of double number. The value to the left of the decimal point relates to the date today (12th Dec) is 42716, tomorrow will be 42717. 0 is 30th Dec 1899 and has been incremented since then.

time is the fractional part. The time now is 00:22:00 and is the number of seconds so far today divided by the number of seconds in a day (86400) - i.e. 0.0156944444

so a date time for 12/12/2016 00:22:00 is 42716.0156944444

what you see 12/12/2016 or 12th Dec, whatever is just a special format of that number

So to answer your question, just add the time value you want - and change the format property so you can see it - suggest in the table field properties, but you may also need to change it in any prexisting forms and reports

[JeroenMioch]

Ok thats clear, thanks for the information. So basicly the date and time are there, they just are not both displayed.

I did change the field (wich was TEXT notation) to the Date/Time notation in all tables a querys.
However it still just displays the date.

My application needs to spit out a CSV file and the two date fields in it now suddenly have to be DateTime fields.

[CJ_London]

what do you mean by TEXT notation? If you mean it was a text data type then changing it to date/time is the right thing to do. But you also need to set the format property to General Date.

if you are exporting to a text file, you may need to use the format function, to convert the date just as you want it - i.e. Format([myDate],"general date") or Format([myDate],"dd/mm/yyyy hh:mm:ss")

[JeroenMioch]

Even if i edit the source document (which is an excel sheet) to display a time, when i past that into my table (date/Time field) it just removes the time again..
In the notation of this date/Time field i am clearly stating i want General date/Time formatted like so : DD-MM-YYYY HH:MM:SS

[CJ_London]

again - what do you mean by notation? it is not an access property.



Editing the source document will have no effect - you are just changing the excel property, which has no effect on an access property - and if you are exporting from excel, properties are not exported, so you will need to use the excel text function to convert the date numeric value to text

[JeroenMioch]

Yes general date is selected from the properties. Still the field displays only date.
I am clueless how to approach this.

[ssanfu]

So you have a table with a field (say) "MyDateTime" with a data type of "Date/Time".

In the field properties at the bottom, there is a property "Format".
If the "Format" property is empty or has "General Date", and you enter just a date, the Time component is defaulted to "00:00:00".

Looking at the table in datasheet view:
With the "Format" property empty or "General Date", the time is NOT displayed, just the date.
If you then edit the field to add a time, say "12:01:00", the date and time WILL be displayed, for those entries that have a time that is not "00:00".

If you want the date AND time to always be displayed, you must set the "Format" property to "dd-mm-yyyy hh:nn:ss" (no quotes), or whatever format you want to see.

[JeroenMioch]

Finally i'm getting somewhere, thanks a million ssanfu !

However i'm running into more problems, as i expected.

So the two dateTime fields in the original table are the right format now.
Now it gets tricky because a new table is created (and deleted once the process is done).
The make table query has the same fields in it and they must have the same notation (dd-mm-yyyy hh:nn:ss).
So i opened the query in design view and set the notation.
Unfortunately the newly created table will not inherit the notation of the query, so i'm stuck with dd-mm-yyyy again.

By the way, the default time is 00:00:00, i don't really care what the time is, as long as it's there along with the date.

Now i figured to alter the notation of the two fields programmatically, do you know a way to do this ?

The table name is tblKRPdeelNemersTotaal

One field is called MinVanvalidfrom and the other MaxVanvalidto

Why the new table has to be created is a little hard to explain, but i tried to cut away the process and as it turns out, it does something vital to the process, so there is no getting around it.

Thanks very much for the help so far guys..

[ssanfu]

Why the new table has to be created is a little hard to explain, but i tried to cut away the process and as it turns out, it does something vital to the process, so there is no getting around it.

That would be strange if you can not use a saved table instead of creating a new table every time..


So this code is executed from a button click. You could merge the code into some other process, if needed, to automate it.
(BTW, is the table name really "tblKRPdeelNemersTotaal"?)

Private Sub Command1_Click()
    Dim dbs As DAO.Database
    Dim tdfNew As TableDef
    Dim strTableName As String
    Dim strFieldName1 As String
    Dim strFieldName2 As String
    Dim resp As Boolean

    strTableName = "tblKRPdeelNemersTotaal"
    strFieldName1 = "MinVanvalidfrom"
    strFieldName2 = "MaxVanvalidto"

    Set dbs = CurrentDb
    Set tdfNew = dbs.TableDefs(strTableName)

    resp = SetAccessProperty(tdfNew.Fields(strFieldName1), "Format", 10, "dd-mm-yyyy hh:nn:ss")
    '    If resp Then
    '        MsgBox "Field 1 success"
    '    Else
    '        MsgBox "Field 1 failed"
    '    End If

    resp = SetAccessProperty(tdfNew.Fields(strFieldName2), "Format", 10, "dd-mm-yyyy hh:nn:ss")
    '    If resp Then
    '        MsgBox "Field 2 success"
    '    Else
    '        MsgBox "Field 2 failed"
    '    End If

End Sub

Add this code to a standard module

Function SetAccessProperty(obj As Object, strName As String, _
                           intType As Integer, varSetting As Variant) As Boolean

    Const conPropNotFound As Integer = 3270

    Dim prp As Property

    On Error GoTo ErrorSetAccessProperty
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    SetAccessProperty = True

ExitSetAccessProperty:
    Exit Function

ErrorSetAccessProperty:
    If Err = conPropNotFound Then
        Set prp = obj.CreateProperty(strName, intType, varSetting)
        obj.Properties.Append prp
        obj.Properties.Refresh
        SetAccessProperty = True
        Resume ExitSetAccessProperty
    Else
        MsgBox Err & ": " & vbCrLf & Err.Description
        SetAccessProperty = False
        Resume ExitSetAccessProperty
    End If
End Function