Convert A Field (Date) In A Table To Julian Date

[Pat Hartman]

Where are you running the code? When you run the code in the form's class module, you should reference form fields by using "Me.yourcontrolname".

When you reference a form's control from a query or a different form, you reference the control as "Forms!yourformname!yourcontrolname"

If the Control is unbound, you need to tell Access that it is a date field by specifying a date format such as "short date". If the control is bound, it MUST be bound to a date data type in order for date functions to work. Some will work against strings but most expect an actual date data type.

 

[isladogs]

Access already has perfectly good functions to return "Julian" date as it is called in many mainframe systems. Let's not quibble about the accuracy of the name.

Format(YourDate, "yy") & DatePart("y", YourDate)

Thanks for that Pat. Nice & simple
So today 08/05/2018 (UK format) is 18128

I always forget about the 'day of year' argument for DatePart and have never needed to use Julian dates in this sense.

 

[lhooker]

Pat/Ridders,

I'm running the code a button on the form. The statement is as follows:

Right(Year([NextPaymentDate]), 2) & DatePart("y", [NextPaymentDate])

Unfortunately, I'm not getting a leading zero for two (2) digits dates. If it possible to use the above command for two (2) digits date with a leading zero ?

 

[isladogs]

You mean day 6 in 2018 is showing as 20186 and you want 201806?
If so, try this

Right(Year([NextPaymentDate]), 2) & Format(DatePart("y", [NextPaymentDate]),"00")

Or if you want three digits for day number e.g. 2018006 ; 2018056 ; 2018325 then

Right(Year([NextPaymentDate]), 2) & Format(DatePart("y", [NextPaymentDate]),"000")

 

[lhooker]

Ridders,

That worked . . . Thanks ! ! !

 

[isladogs]

Excellent.
Which of those did you actually mean?