build macro for special date format (1 Viewer)

[zjpm]

I am trying to build a macro which will convert the current date into julian date with last digit of the year first. i.e. Feb 4,2000 would be displayed as 0035. may 14,1999 would be displayed as 9134. I have been able to get very close in converting the last digit of the year and the julian equivalant of the date. My hurdle is adding the two together as strings. The software continues to recognize the information as numeric and drops the leading zeros to give me 29 for Jan 29,2000 which should be 0029. This will be used to track a purchasing database used as the date portion of a Purchase order Number. Anyone know how to make the strings play along? Thanks in advance

 

[Travis]

This is a great problem, I also needed this and found no answers any where, but I did manage to solve it. First the Julian Field Needs to be a text field (Number fields drop the leading Zeros and since every ten years the leading number will be zero the problem is obvious)

Next I used Code Instead of a macro. I found it to be a little easier.

Public Function ConvertToJulian(ByVal dDate as Date) as String
stDays as String
If IsDate(dDate) then
stDays = Right("0000" & Datepart("y",dDate),4)
ConvertToJulian = Right(DatePart("yyyy",dDate),1) & stdays
End Function

Hope this helps