2

I have a text file created by another program that I have imported into MS Access 2003. The date & time fields are split into 3 seperate fields and I would like to create 1 date/time field. Here is an example of the data:

YEAR,DAY_NUMBER,TIME
2002,231,223405
2004,117,000000

YEAR: The year in YYYY format

DAY: The day of the year. e.g. 001 would represent 1st of January; 034 would be the 3rd of February

TIME: The time in HHMMSS

How do I create an additional field with the corresponding date/time value? Thanks in advance.

B McKenzie
  • 107
  • 4

3 Answers3

2

This example assumes your table is named MyTable, its YEAR and DAY_NUMBER fields are both numeric data types, the TIME field is text, and you added a Date/Time field called date_time.

Try an update query with the DateSerial() and TimeSerial() functions.

UPDATE MyTable
SET date_time = DateSerial([YEAR],1,DAY_NUMBER)
    + TimeSerial(Left([TIME],2), Mid([TIME],3,2), Right([TIME],2));

Edit: If I misunderstood your intention, and you don't actually need or want to store the Date/Time value, you can just derive it when needed with a SELECT query.

SELECT
    DateSerial([YEAR],1,DAY_NUMBER)
        + TimeSerial(Left([TIME],2), Mid([TIME],3,2), Right([TIME],2))
        AS date_time
FROM MyTable;
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • +1 I had no idea you could use 1 for the month and then just use > 31 in the day part. That is genius ^_^ – Matt Donnan May 11 '12 at 08:07
  • @MattDonnan In another question recently, you and Brian Camire motivated me to take another look at `DateSerial()`. I was curious to see what it did with a bogus date ... `DateSerial(2012,2,31)` ... looked like its response could be useful. :-) – HansUp May 11 '12 at 13:44
  • @HansUp Absolutely, that is something i'll not forget in the future. Out of curiosity, which question was this... if you can still remember how to find it lol? – Matt Donnan May 12 '12 at 18:57
  • @MattDonnan http://stackoverflow.com/questions/10502802/cdate-type-mismatch-error/10503155 – HansUp May 12 '12 at 19:04
  • 1
    @HansUp ^_^ Brian beat me to the answer on that occasion – Matt Donnan May 13 '12 at 20:35
  • Thanks Hans... this is what I went with in the end. – B McKenzie May 25 '12 at 03:37
1
' *********************************************************************
' FUNCTION: CJulian2Date()
'
' PURPOSE: Convert a Julian day to a date. The function works with
'          dates based on the Gregorian (modern) calendar.
'
' ARGUMENTS:
'    JulDay: The ordinal day of a year. Between 1 and 365 for all
'            years, or between 1 and 366 for leap years.
'
'    YYYY: A three or four digit integer for a year that is within the
'          range of valid Microsoft Access dates. If YYYY is omitted,
'          then YYYY is assumed to be the year of the current system
'          date.
'
' RETURNS: A date for a valid Microsoft Access year and Julian day,
'          or a Null value for an invalid Julian Day.
' *********************************************************************

Function CJulian2Date (JulDay As Integer, Optional YYYY)
    If IsMissing(YYYY) Then YYYY = Year(Date)
    If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _
      > 9999 Then Exit Function
    If JulDay > 0 And JulDay < 366 Or JulDay = 366 And _
      YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then _
        CJulian2Date = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy")
End Function

Source: http://support.microsoft.com/kb/209922

Kevin Collins
  • 1,453
  • 1
  • 10
  • 16
0

Take the year and convert to Jan 1 of that year. Add the number of days to that and convert to a date format. You could put this in a query.

CalcDate: CDate(CLng(CDate("01/01/" & [Year]))+[day]-1)

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • 1
    I don't understand why you used `CDate()` twice and `CLng()` instead of `CDate("01/01/" & [YEAR]) + [DAY_NUMBER] -1` – HansUp May 11 '12 at 03:01
  • @HansUp - I was building the pieces of this and testing as I went along and forgot an integer will be treated as days and can be added to a date and still get a date in Access. The CLng() aided in knowing I was getting the number of days. – JeffO May 11 '12 at 15:15