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

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.
 
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.
 
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 ?
 
You mean day 6 in 2018 is showing as 20186 and you want 201806?
If so, try this
Code:
Right(Year([NextPaymentDate]), 2) & Format(DatePart("y", [NextPaymentDate]),"00")

Or if you want three digits for day number e.g. 2018006 ; 2018056 ; 2018325 then
Code:
Right(Year([NextPaymentDate]), 2) & Format(DatePart("y", [NextPaymentDate]),"000")
 

Users who are viewing this thread

Back
Top Bottom