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

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!

"deeds" wrote:

> 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

On Thu, 17 Jan 2008 07:56:00 -0800, deeds <deeds@discussions.microsoft.com>
wrote:

>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]

On Thu, 17 Jan 2008 08:55:00 -0800, deeds <deeds@discussions.microsoft.com>
wrote:

>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]

Works great! Thanks!

"John W. Vinson" wrote:

> On Thu, 17 Jan 2008 07:56:00 -0800, deeds <deeds@discussions.microsoft.com>
> wrote:
> 
> >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]
> 
> 

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!

"John W. Vinson" wrote:

> On Thu, 17 Jan 2008 08:55:00 -0800, deeds <deeds@discussions.microsoft.com>
> wrote:
> 
> >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]
> 

On Thu, 17 Jan 2008 11:16:03 -0800, deeds <deeds@discussions.microsoft.com>
wrote:

>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]

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" wrote:

> On Thu, 17 Jan 2008 11:16:03 -0800, deeds <deeds@discussions.microsoft.com>
> wrote:
> 
> >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]
> 

On Fri, 18 Jan 2008 07:56:06 -0800, deeds <deeds@discussions.microsoft.com>
wrote:

>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]