Copy into 2nd field as Julian

[smyeong]

Hi, all.
I have inserted a new module as following :

Option Explicit
' RETURNS: A three digit Julian day as a string.
Function Date2Julian(MyDate As Date) As String
Date2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, 31), "000")
End Function

This module is to turn my date into 3 digit number

I was wondering how i can copy the dateA column into another B column as Julian ? I tried below method but it failed somehow.

Private Sub dateA_AfterUpdate
Me.ItemJDate = DoCmd.OpenFunction(Date2Julian(ItemDate))
Me.ItemJDate.Requery

Please rectify me if had anything wrong ?

 

[Mile-O]

Why would you want to store the Julian date in another field if you already have the original date and the means to calculate the Julian date at any time?

 

[smyeong]

Why would you want to store the Julian date in another field if you already have the original date and the means to calculate the Julian date at any time?

Thank you. 1st of all, I need that date to be in Julian format then i'll add year number to make it unique for numbering. That was requested by my customer instead of using autonumber.

Thanks

 

[Jon K]

It is:

Private Sub DateA_AfterUpdate()

  Me.ItemJDate = Date2Julian(DateA)

End Sub

Alternatively you can use the built-in DartPart() function:-

Private Sub DateA_AfterUpdate()

  Me.ItemJDate = Format(DatePart("y", Me.DateA), "000")

End Sub

 

[Mile-O]

I'd still advise an Autonumber and simply Index the date.

 

[Pat Hartman]

Using "intelligent" keys frequently causes problems because at some point the intelligence breaks down. For example, you are asking how to create a julian date that you can use as a primary key but what happens if you need to add more than one record on any given date? Even if that is not a requirement now, could it be a requirement in the future? Don't box yourself into a sitiation where the "intelligence" in the "intelligent" key needs to be corrupted inorder to add additional values in a range.