Date picker fills in text box with Julian date in same form.

[Etoimos]

I have a Date field in my form that uses a date picker. I would like to have whatever is selected in that date picker field to automatically calculate the Julian date and put that in a field in the same form. How would I go about doing that?



I've tried setting the Control Source of the Julian Date field to =[thetable]![DateField], but I can't find where to format the JulianDate field.

For formatting I've seen a couple of options listed:

JulianDate = date - DateSerial(Year(date), 1, 1) + 1

=CDate2Julian()

CLng(Format(Date, "y"))

[ranman256]

(2 text boxes)
have the user set txtDate (picker)
but the txtJulian controlSource property would be:
=CDate2Julian(txtDate)

self converting.

[Etoimos]

(2 text boxes)
have the user set txtDate (picker)
but the txtJulian controlSource property would be:
=CDate2Julian(txtDate)

self converting.

I'm assuming that the "self converting" is not part of the the code. Without that in it, this is not working for me. When I set the date in ExecutionDate nothing happens in the JDay field. It just continues to show #Name? in the text box field.

[ranman256]

i havnt seen your CDateJulian function , but it should return the converted value.
and it must be PUBLIC if not in the form.

use the name of the 'text box' the user enters the date (not the field). Mine is txtDate.

it works just fine here.

Function CDate2Julian(varMyDate As Variant) As String
If Not IsDate(varMyDate) Then Exit Function
CDate2Julian = Format(varMyDate - DateSerial(Year(varMyDate) - 1, 12, 31), "000")
End Function

[Etoimos]

i havnt seen your CDateJulian function , but it should return the converted value.
and it must be PUBLIC if not in the form.

use the name of the 'text box' the user enters the date (not the field). Mine is txtDate.

it works just fine here.

Function CDate2Julian(varMyDate As Variant) As String
If Not IsDate(varMyDate) Then Exit Function
CDate2Julian = Format(varMyDate - DateSerial(Year(varMyDate) - 1, 12, 31), "000")
End Function

Umm, do I have a CDateJulian function? lol Is that something standard in Access? Sorry, I should have stated in the first post that I'm completely new to Access. Looking at the code you posted, it appears I'm going to need a function setup for CDate2Julian and some type of variable for MyDate?

[davegri]

Design view of form. The top textbox name is txtEnterDate

[Etoimos]

Design view of form. The top textbox name is txtEnterDate

That worked perfectly! Thank you.

[Etoimos]

Actually, if I wanted it to return "001" instead of "1" for January 1st, what would I need to modify? doing 'yyy' returns the two digit year and then the single digit day.

[kd2017]

Format([txtEnterDate], "yy") & Format(DatePart("y", [txtEnterDate]), "000")

[Etoimos]

Format([txtEnterDate], "yy") & Format(DatePart("y", [txtEnterDate]), "000")

Thanks. That second one worked for me. The first returned the two digit year, which would be useful if I needed to differentiate across multiple years. So good knowledge.

[kd2017]

The whole thing together should return julian date in the form of YYDDD. I'll break it down

'This will format a date to just show the last two digits of the year
Dim TwoDigitYear As String
TwoDigitYear = Format([txtEnterDate], "yy")


'This will extract the day of the year from a date
Dim DayOfYear As Integer
DayOfYear = DatePart("y", [txtEnterDate])


'This will format the day of the year to pad with leading zeros
Dim ThreeDigitDay As String
ThreeDigitDay = Format(DayOfYear, "000")


'This will tie it all together
Dim jDate As String
jDate = TwoDigitYear & ThreeDigitDay

'WHICH IS THE SAME AS
jDate = Format([txtEnterDate], "yy") & Format(DatePart("y", [txtEnterDate]), "000")

[Etoimos]

I've got an added on to this Julian date field I'm working on. I need the date that gets placed into this field stored in a table called RunResultData. Normally I just select the field I want to save the data to as the Control Source, but with the date code in the control source I can't do this... or better, I don't know how to do that.

[kd2017]

It's generaly frowned upon to save calculated fields. If your database has the information to calculate/query the field, then just calculate the field. Otherwise you're essentially storing redundant information. Also, if the fields behind the calculation change after the calculation has been saved then there is the potential that the saved field wont get properly updated.

https://btabdevelopment.com/why-you-...in-the-tables/

Consider this information. If you STILL want to do it anyway we can help you... even if it's just an exercise for you.

[Etoimos]

Those are good points in that article. I think I can get by without saving the JDay into my table. I'm going to think out loud here in hopes that someone will point out any flaws in my thinking...

The reason I need the JDay at all is that in my career people use Gregorian and Julian date interchangeably. All of the data produced by our tests is referenced by the date the test was ran on. So one person might request data from Event X on day 9/16/2019 and another person might request that same data, but might request it as data from Event X on JDay 259. The form where I am calculating the JDay is mainly so the test operator knows what JDay the data is being created on (situational awareness really). It seems like I could also calculate the JDay in another query or form used to look up the data at a later time.

How hard would it be to have a form that a person could enter either the Gregorian date or the Julian date and have that form search through the stored Gregorian dates in my tables? In other words, is it hard to get Access to do the reverse date calculation on the fly?