Convert Julian to Std Date

[deeds]

I am trying to convert the entry in a text box (which is a std date 01/01/08)
to Julian date. The format for the Julian date is: 108001.

So, in the end I want: When user enters 1/1/2008 in a text box on a form
another text box on the same form shows the Julian date in this form 108001

Please provide what format I need to enter in the text box so that the
Julian date appears correctly. Thanks

 

[deeds]

Let me explain what I need and maybe someone will have a better idea:

A user chooses a date from a calendar on a form...which populates a text box
with the date on the form. From that date I want another text box on the
form to show the Julian date. Now, I also would like that Julian date to be
stored in a table somehow so that a query can use that Julian date in
criteria. I don't want the query to look to the form for the Julian date,
because then the form needs to be open each time the query runs.
So, ultimately I want the date that the user chooses to be converted to
Julian and then stored so that a query can access it. Thanks!

 

[John W. Vinson]

I am trying to convert the entry in a text box (which is a std date 01/01/08)
to Julian date. The format for the Julian date is: 108001.

So, in the end I want: When user enters 1/1/2008 in a text box on a form
another text box on the same form shows the Julian date in this form 108001

Please provide what format I need to enter in the text box so that the
Julian date appears correctly. Thanks

Set the control source of the Julian date textbox to

=IIF([realdate] > #1/1/2000#, "1", "") & Format([realdate], "yy") &
Format(Format([realdate], "y"), "000")


John W. Vinson [MVP]

 

[John W. Vinson]

A user chooses a date from a calendar on a form...which populates a text box
with the date on the form. From that date I want another text box on the
form to show the Julian date. Now, I also would like that Julian date to be
stored in a table somehow so that a query can use that Julian date in
criteria. I don't want the query to look to the form for the Julian date,
because then the form needs to be open each time the query runs.
So, ultimately I want the date that the user chooses to be converted to
Julian and then stored so that a query can access it. Thanks!

If you're assuming that you must store the Julian date redundantly in your
table in order to search against it, your assumption is wrong.

Store the date; to search it using a Julian date input for criteria use an
expression like

DateSerial(1900 + Val(Left([Enter JDate:], Len([Enter JDate:] - 3), 1,
Val(Right([Enter JDate:]), 3))


John W. Vinson [MVP]

 

[deeds]

Works great! Thanks!

I am trying to convert the entry in a text box (which is a std date 01/01/08)
to Julian date. The format for the Julian date is: 108001.

So, in the end I want: When user enters 1/1/2008 in a text box on a form
another text box on the same form shows the Julian date in this form 108001

Please provide what format I need to enter in the text box so that the
Julian date appears correctly. Thanks

Set the control source of the Julian date textbox to

=IIF([realdate] > #1/1/2000#, "1", "") & Format([realdate], "yy") &
Format(Format([realdate], "y"), "000")


John W. Vinson [MVP]

 

[deeds]

Thanks. Can you explain more your answer...the sample criteria you posted,
would I put that in the criteria of the query that is looking for a Julian
date? If so, is this converting the standard date entered in the form to
Julian?

I have it set up now that when user picks a date, that std date appears in a
text box which is a field in a table. So, when user closes the date choose
form, the dates are now stored in the table. Now I want to create some
criteria that could look to those dates and convert them to Julian for the
query to pull the data. Your sample you posted may do it, however, I get the
message of
"wrong # of arguments"....thanks again!

A user chooses a date from a calendar on a form...which populates a text box
with the date on the form. From that date I want another text box on the
form to show the Julian date. Now, I also would like that Julian date to be
stored in a table somehow so that a query can use that Julian date in
criteria. I don't want the query to look to the form for the Julian date,
because then the form needs to be open each time the query runs.
So, ultimately I want the date that the user chooses to be converted to
Julian and then stored so that a query can access it. Thanks!

If you're assuming that you must store the Julian date redundantly in your
table in order to search against it, your assumption is wrong.

Store the date; to search it using a Julian date input for criteria use an
expression like

DateSerial(1900 + Val(Left([Enter JDate:], Len([Enter JDate:] - 3), 1,
Val(Right([Enter JDate:]), 3))


John W. Vinson [MVP]

 

[John W. Vinson]

Thanks. Can you explain more your answer...the sample criteria you posted,
would I put that in the criteria of the query that is looking for a Julian
date? If so, is this converting the standard date entered in the form to
Julian?

I have it set up now that when user picks a date, that std date appears in a
text box which is a field in a table. So, when user closes the date choose
form, the dates are now stored in the table. Now I want to create some
criteria that could look to those dates and convert them to Julian for the
query to pull the data. Your sample you posted may do it, however, I get the
message of
"wrong # of arguments"....thanks again!

Please post the actual SQL statement you're using. This code was only
partially tested...

John W. Vinson [MVP]

 

[deeds]

All I did was paste your criteria of:
DateSerial(1900 + Val(Left([Enter JDate:], Len([Enter JDate:] - 3),
1,Val(Right([Enter JDate:]), 3))

I put this in the criteria line of the field in the query that needs a
Julian date. My understanding is I should be able to write some kind of
criteria that will look at the Gregorian date field in a table and convert it
to Julian for the query. So, I pasted your formula in the criteria line to
begin to work with, however, I can't move off of the criteria area without
the message ...."wrong # of arguments".
Thanks

 

[John W. Vinson]

I put this in the criteria line of the field in the query that needs a
Julian date. My understanding is I should be able to write some kind of
criteria that will look at the Gregorian date field in a table and convert it
to Julian for the query.

Not quite: it will accept a search criterion in Julian format and recast it
into a date for searching the Date/Time field in the table. What's in the
table is just a number, a count of days and fractions of a day from a start
point - it's not particularly "Gregorian".

So, I pasted your formula in the criteria line to
begin to work with, however, I can't move off of the criteria area without
the message ...."wrong # of arguments".

Sorry, my error: misplaced parentheses. Try

DateSerial(1900 + Val(Left([Enter JDate:], Len([Enter JDate:]) - 3)),
1,Val(Right([Enter JDate:], 3)))


John W. Vinson [MVP]

 

[John Woolard]

The code you provided worked incredible - thank you. However, I also need to have this Julian date recorded into a table. With the Control Source for the text box set to this code, I need to associtae that data so it can be stored in a table for data tracking later. How would I perform this function?

Many thanks.

 

[Jeff Boyce]

John

Your post came through as a new post, not a response in an earlier thread.
It's a bit hard to connect-the-dots <g>.

If you already have a date/time value, and have code that can convert it to
a Julian date, you don't need to store the calculated Julian date. Just use
your code to derive the Julian value whereever (e.g., in a form, in a
report) you need it.

There are some very good reasons NOT to store a calcuated value like this.
For instance, if someone changes the original date (fixing a data entry
error) or changes the (calculated) Julian date, your two dates are no longer
in sync. Always calculating/deriving the Julian prevents any risk of data
integrity through poor synchronization.

Regards

Jeff Boyce
Microsoft Office/Access MVP