Date storage

[torch_music]

I am reading in a log file which contains file conversion stats for another
program but I am having a problem storing dates and times. The second and
third field in the file is the date and time that the file was converted.
The date is in the format day of the year and the time is in the format
HH:MM. My code is:

Dim CurrentDay As Integer
Dim CurrentYear As Integer
Dim tmpInt As Integer
Dim tmpStr As String
Dim tmpStartYear As String
Dim tmpDate As String
Dim tmpTime As String

'Get current day in day of year format
CurrentDay = Format(Now, "y")
'Get current year
CurrentYear = Format(Now, "yyyy")

'Conversion Date & Time
'Get conversion day from buffer
tmpInt = Mid(strBuffer, 5, 3)
'If conversion day is greater than today, then file was
converted last year
If tmpInt > CurrentDay Then
tmpStartYear = "01/01/" & CurrentYear - 1
'Get Gregorian date by adding no of days to start of year
minus 1
tmpDate = Format(DateAdd("d", tmpInt - 1,
CDate(tmpStartYear)), "dd-mmm-yyyy")
'Get conversion time from buffer
tmpTime = Mid(strBuffer, 9, 5)
'Join both strings together to get a full date/time
tmpStr = tmpDate & " " & tmpTime
'Debug.Print tmpStr
'Convert string to date/time format and store in var for
later insertion
ConvStats.ConvDate = CDate(tmpStr)
'Debug.Print ConvStats.ConvDate
Else
'File converted this year
tmpStartYear = "01/01/" & CurrentYear
'Get Gregorian date by adding no of days to start of year
minus 1
tmpDate = Format(DateAdd("d", tmpInt - 1,
CDate(tmpStartYear)), "dd-mmm-yyyy")
'Get conversion time from buffer
tmpTime = Mid(strBuffer, 9, 5)
'Join both strings together to get a full date/time
tmpStr = tmpDate & " " & tmpTime
'Debug.Print tmpStr
ConvStats.ConvDate = CDate(tmpStr)
'Debug.Print ConvStats.ConvDate
End If

When I run this with the debugs uncommented the dates that are being passed
look correct, eg if first print shows 01-Jan-2007 05:55:00 the second print
shows 01/01/2007 05:55:00. However when I go into the datasheet view of the
table and sort on the conversion date ascending, the display shows all the
data for 01/01/2007, followed by 13/01/2007 and a bit later on data for 2nd
Jan as 01/02/2007, 3rd Jan as 01/03/2007, etc up to the 12th Jan, which seems
to me to indicate that the date is being stored as the 1st Feb, 1st Mar, etc
instead of 2nd Jan, 3rd Jan. Can anyone shed any light on this behaviour?
Thanks
Peter

 

[Jeff Boyce]

Peter

Is there a chance this is related to the difference between US date format
(mm/dd/yyyy) and "European" date format (dd/mm/yyyy)?

What is the default date format on your PC?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[torch_music]

The default date format is UK. I thought that when I formated the date in
dd-mmm-yyyy format before converting it to an actual date that would have
sorted out any ambiguity over whether it was a mm/dd or a dd/mm, but it
doesnt seem to have done. I created a form based on the import table and
displayed the date field in Long Date format and that confirmed that Access
thinks I have entered data for 1st Feb, 1st March etc instead of 2nd Jan, 3rd
Jan.
Thanks

 

[torch_music]

Got it.
I changed the data type of the variable ConvStats.ConvDate to a string
instead of a date, then in the line
ConvStats.ConvDate = CDate(tmpStr) I took out the conversion and stored it
as a string, then when I do the insert I put the ConvStats.ConvDate between
hashs when I am passing the value. And the dates go in correctly. Dont know
why, but they do.
Thanks to anyone who spent time scratching their heads over this.
Peter