DateTime as number problem (1 Viewer)

[Ruzz2k]

Hi, I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.

Then I am linking the file to the database and subsequently doing a lookup on the datetime on the access form against this table.

The excel file when formatted from vba in access shows the wrong number

datetime = 03/05/2013 11:26

database number = 41397.4763888889

Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)

now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.

Any Ideas anyone,
Also I have tested the code with manually editing the excel file and this works fine.

Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)
 
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
 
With XLApp
.Application.DisplayAlerts = False
 
.Workbooks.Open s
For i = 1 To Sheets.Count
Sheets(i).Name = "Sheet" & i & ""
Next
Columns("A:A").Select
Selection.NumberFormat = "0.000000000000000"
.ActiveWorkbook.SaveAs Left(s, InStrRev(s, ".") - 1) & ".xls", FileFormat:=xlNormal
 
.ActiveWorkbook.Close
 
.Application.DisplayAlerts = True
End With
 
DoCmd.TransferSpreadsheet acLink, , "Sheet1", Left(s, InStrRev(s, ".") - 1) & ".xls", True, "Sheet1!A14:C43"
DoCmd.OpenForm ("frmList1")
Forms![frmList1]![Text0] = s
 
End Sub

also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows

Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String
Dim dtB As Double
Dim x As Date
x = Forms![frmCalibration]![Calibration Date]
dtA = x & " " & Forms![frmCalibration]![RD Time1]
dtB = CDate(Format(dtA, "dd/mm/yyyy hh:mm:ss"))
MsgBox (dtB)
'Forms![frmCalibration]![Ref 1 Reading1] = Nz(DLookup(w, "[Sheet1]", "[Date Time dd/mm/yyyy]=" & dtB))
Forms![frmCalibration]![Ref 1 Reading1] = Nz(DLookup("[Sample temp]", "[Sheet1]", "[Date Time dd/mm/yyyy]=" & dtB))
End Sub

 

[Rx_]

Help me out with this. Excel (and Access) stores date/time as the number of days (and fractions of days) as you indicated.
I never use the canned function TransferSpreadsheet. It is a binary function and the user is at the mercy of its results. That said, it is interesting that it is off an entire day.
This is more along the line of my usage from Access to Excel:
550 Debug.Print " sql string = " & strSQLSundries ' for test purposes of SQL string
560 Set rsDataSundries = CurrentDb.OpenRecordset(strSQLSundries, dbOpenSnapshot, dbReadOnly)
570 intRowPos = 6 ' Sets starting Row for data in Excel - reference fields to this
590 ObjXL.DisplayAlerts = False ' Turn off Display Alerts
600 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataSundries
620 intMaxRecordCount = rsDataSundries.RecordCount - 1 ' - use for max rows returned in formatting later

The Copy from Recordset method on line 600 has always appeared to work.

Would be interested if you can expand a little more on this interesting issue.

 

[Ruzz2k]

ok I have looked more carefully and the number is 41338.4763888889 which means the date is being converted as 05/03/13 (American) instead of 03/05/13(UK) i am having a look at how to fix this in my code above. help is appreciated in case I cant fix it alone, I am presuming I need to format the date as American before converting it to a number format.
thanks

 

[Ruzz2k]

OK I got it to work by changing dtB = CDate(Format(dtA, "dd/mm/yyyy hh:mm:ss"))
to dtB = CDate(Format(dtA, "mm/dd/yyyy hh:mm:ss"))

Now I have two final problems
One excel does not quit and the instance stays open in the background and two I don't know how to unlink the excel file from the database any ideas?

Thanks

 

[Rx_]

Great catch on the right way vs the Britt way. (just a joke)
Personally I prefer the YYYY-MM-DD-HH-MM-SS Report Name format in naming my Excel reports. They auto sort in a directory.

Glad you caught it. I refer to it as an orphan process. It is just unclaimed in memory.
The key is to destroy all object variables. You didn't set the variable to Nothing.

This should give you the idea.
objExcel.ActiveWorkbook.Close True, "C:\BegVBA\MnthSale 2013-11-13.XLS" ' Saving & closing Workbook
' Close Excel and free the memory
Set objChart = Nothing
Set objExcel = Nothing
DoCmd.Hourglass False
objExcel.Quit' Leave the Excel application
Exit Sub

 

[TJPoorman]

Correct me if I'm wrong, but don't you need to run the .Quit command before releasing the variables?

Great catch on the right way vs the Britt way. (just a joke)
Personally I prefer the YYYY-MM-DD-HH-MM-SS Report Name format in naming my Excel reports. They auto sort in a directory.

Glad you caught it. I refer to it as an orphan process. It is just unclaimed in memory.
The key is to destroy all object variables. You didn't set the variable to Nothing.

This should give you the idea.
objExcel.ActiveWorkbook.Close True, "C:\BegVBA\MnthSale 2013-11-13.XLS" ' Saving & closing Workbook
' Close Excel and free the memory
Set objChart = Nothing
Set objExcel = Nothing
DoCmd.Hourglass False
objExcel.Quit' Leave the Excel application
Exit Sub

 

[The_Doc_Man]

You always want to set all object variables to NOTHING before you use QUIT because otherwise you have a dangling object (data structure) in memory charged against your process. That's why the process dangles. And once you do the QUIT you no longer have any way within your process to do anything constructive (well... technically in this case, DEstructive) with those objects. That's because once you do the QUIT, your process goes into a process run-down state except that Access doesn't have automatic "destructors" for dynamically created objects. It does for the static data structures in class modules - but not for dynamics.

As to the date difference, I never quite figured out why it was done this way, but the whole thing has to do with the "reference date." In Access and in Excel, the same general concept is used, but there are differences in the details. Other operating systems do this, too. Each of them, of course, uses a different reference. (Let's not have standardization, now, shall we?)

Date/Time is usually a typecast of something else. Most of the time, operating systems have this reference date and a counter. The counter represents some number of clock ticks since the reference date and then the O/S date/time formatting library turns that counter into a number of days and fractions since the reference. On Windows, you will see either Midnight 31 Dec 1899 or Midnight 1 Jan 1900 as the reference date - and yes, I really did mean "EITHER" - because that is the source of that one day of difference you observed. For the record, UNIX uses Midnight, 1 Jan 1970 and OpenVMS uses Midnight, 17-Nov-1858. (For OpenVMS, it's a long story.)

In any case, when you convert the counter to a floating fraction, the integer part is the days since the reference and the fractional part is the fractions of a day since midnight. The resolution of the counter tells you what you have to use to convert click ticks to days. Usually in Access you want to treat date-fractions as DOUBLE, which gives you a pretty good range. If you do the typecast, CDATE of a DOUBLE variable, that works just fine as input to the FormatDateTime(time,format) function.

This is actually a reasonable choice for a representation. Days-since-reference any time in the next decade or so will still be less than 65536 (64K) days since reference, or 16 bits worth. The day has 86400 seconds in it, so that's between 64K and 128K, another 17 bits. That's 33 bits. A DOUBLE can hold 50+ bits, so you can express fractions of a second if you do your own mapping of the fractions. Unfortunately, the date/time routines within Access will not resolve the fractions because they want to stop at integer numbers of seconds even though the DOUBLE can easily give you precision to centiseconds or milliseconds.

 

[Ruzz2k]

Hi thanks for the explanation, unfornately I cannot get the excel instance to close, if I put XL.Quit after the set XLApp as nothing then I get and error.

Could you be kind enough to explain it with my code in the first box, as I just cant get it to close the instance, im pretty sure the workbooks close so I dont quite understand why the instance wont.

Thanks