Converting to a date

[mtepfer]

We are transfering data from a Pick database to access via a TXT file. The problem is in Pick the internal date for an attribute is stored as the number of days from 12/31/67. For example 1/26/05 is stored as 13541.

My question is there a formula I can use to take the number that is imported into the access table add it to 12/31/67 and come up with a date.
Thanks for any help

Mike

 

[RoyVidar]

Shouldn't be to difficult. 12/31/67 in Microsoft Access, is stored as 24837, so it should be a simple addition, then voila.

Try for instance in the immediate pane:

[tt]? format(val(24837)+val(13541),"mm/dd/yyyy")[/tt]

- so there's your formula;-)

[tt]update mytable set mydate = mydate + 13541[/tt]

Roy-Vidar

 

[RoyVidar]

Hmpf wrong number

[tt]update mytable set mydate = mydate + 24837[/tt]

Roy-Vidar

 

[lespaul]

Are you sure that will work correctly Roy?

I thought that MS stored dates as the number of days since 12/31/1899. So, 12/31/67 is 24837 days from 12/31/1899. So the number of days between 12/31/1899 and 1/6/05 is 38742 - this would need to be the number inserted in to the Access database to have the correct "date".

If PICK is sending 1/6/05 as 13541 then, she would need to add 25201 to it to get the correct MS date, right?

PICK 1/6/05 = 13541
+25201
MS 1/6/05 = 38742




Leslie

 

[RoyVidar]

Nope - I'm far from sure. In fact I just hate doing calcualations on date/time, and fail just about every time.

What happens (which year) if you try formatting your number to date?

[tt]? format(38742,"mm/dd/yyyy")[/tt]

My reasoning:

[tt]PICK 12/31/67 = 0
+24837
MS 12/31/67 = 24837[/tt]

[tt]PICK 1/26/05 = 13541
+24837
MS 1/26/05 = 38378[/tt]

Roy-Vidar

 

[MzKitty]

We are extracting data from an Epicor SQL database and have a julian date module that we use. Here is an example of the section of the module that we use. Now our julian date is from the beginning of time (for some reason it starts in B.C.), but we do a query and make a date field from the action.
In the query, this is our code.
DateEnteredX: JulLngToMoEnd([date_applied]) (date_applied is the julian date and it is converted into the month end date).
Function JulLngToMoEnd(lngJul As Long) As Variant

Dim intMonth As Integer
Dim intYear As Integer

If lngJul > 693594 Then

intMonth = DatePart("m", CVDate(lngJul - 693594))
intYear = DatePart("yyyy", CVDate(lngJul - 693594))

JulLngToMoEnd = DateSerial(intYear, intMonth + 1, 1 - 1)

End If

End Function


good luck
Cathy

 

[mtepfer]

Thanks for the advice ended up going a different way, reason being is the whole process is automated for the user, they type the number of the report to run from our system (running on PICK database) then the data all appears in excel for them using macros and querys in access. no interaction for them to many headaches.

Did it by creating a query to append using the start date and adding the number of days from the value.

UPDATE WorkOrders SET WorkOrders.[Request Date] = DATESERIAL(1967,12,31)+[WorkOrders].[Request Date], WorkOrders.[Date Complete] = DATESERIAL(1967,12,31)+[WorkOrders].[Date Complete];

Mike