Change a Number to a Date

[bethabernathy]

Hi - I have a field in access named "readLastDate" that contains a number, for example: "721486825". I need to make this number a date, but not only do I need to make it a date I need to add this date to the number "1\1\1980 12:00:00 AM". So "721486825" is the number of seconds since "1\1\1980 12:00:00 AM" which need to be added and turned into the current date.

I was told that there is a DateAdd function that might work. Any ideas? Thanks, Beth beth@integratedresourcemgmt.com

 

[gol4]

You are corrrect the dateadd function should do it

?dateadd("s",721486825, #1/1/1980#)
11/11/2002 1:00:25 PM

 

[MichaelRed]

Although I'm SURE that simply looking up DateAdd in HELP would have been somewhat faster and more complete:

? Dateadd("s", 721486825, #1/1/1980#)
11/11/02 1:00:25 PM

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

also, a bit more generically:

Public Function basSecs2Date(lngSec As Long, Optional DtIn As Variant) As Date

    'Michael Red    11/13/02    Tek-Tips thread701-404762

    Dim MyDt As Date

    If (IsMissing(DtIn)) Then
        MyDt = #1/1/1980#
     Else: MyDt = DtIn
    End If

    basSecs2Date = DateAdd("s", lngSec, MyDt)
End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bethabernathy]

Hi - I did look up the date add function in Microsoft access. Microsoft says it goes:

DateAdd("d", 30, [OrderDate])

I am getting somewhere, but I need something like this:

DateAdd("s",[readLastDate],[SubscribedDate])

This isn't working. But I need to get it so that where the field "readLastDate" is a number that adds onto another field "SubscribedDate" that is another number field and turns it into a date. Is this possible? Thanks, Beth beth@integratedresourcemgmt.com

 

[gol4]

I think you are close but you want subscribed dates rowsource to =

DateAdd("s",[readLastDate],#1/1/1980#)

 

[bethabernathy]

Hi - I have that one working, but the #1/1/1980# is not the same for each record. I need to find a way to pull the number for the "1/1/1980" from the field "SubscribedDate" as that field changes for each record. I wonder if this is possible or if I need to do something like make a query off this query? Thanks, Beth beth@integratedresourcemgmt.com

 

[MichaelRed]

Re-READ my (second) post. It can / will /does do it.

Re-Read the help information. Somewhere (not in specific toopics like dateadd), it fairly clearly states that they use literal arguments for simplicity and calrity, but that any ARGUMENT of the correct (data) type may be substuited.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bethabernathy]

Great Thanks! I really appreciate it. Beth beth@integratedresourcemgmt.com