Julian Date

[red skelton via AccessMonster.com]

Hi everyone,
Trying to display a Julian date in a query. I have the VB code to change a
calender day to a Julian, but how would I use use this code in a query? The
VB code I have came from the Microsoft Access website and it is;

Function CDate2Julian(MyDate As Date) As String

CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _ 31),
"000")

End Function

Can anyone tell me how this would work in a query?

Many Thanks

Red

 

[red skelton via AccessMonster.com]

Hi again,

I found the following exp in a search of this forum. it is: DateSerial(Year
(Date()),1,"NumberofDays"), but when I try it, I get a data mis-match error.
Any idea why? In my table, I have the JulDate set as text vs date so I would
not think that is the problem.

Thanks,

Red

 

[Douglas J. Steele]

Unfortunately, "Julian date" isn't really specific: there are several
definitions.

It's also not clear to me exactly what you want your code to do: the two
snippets you've posted do different things (and, for that matter, the second
one won't work, since you've got a literal text string "NumberofDays" where
you need a numeric value: if you've got a variable named NumberofDays,
remove the quotes from around it)

Assuming your definition of Julian date is the 4 digit year followed by a 3
digit number indicating which day of the year it is, you can generate that
as a string using:

Format(MyDateField, "yyyy") & Format(DatePart("y", MyDateField), "000")

If all you want is the day number, you can use Format(MyDateField, "y") to
get a string, or DatePart("y", MyDateField) to get a number.

To convert the 3 digit number back to the date, as mentioned above you need
to use

DateSerial(Year(Date()), 1, NumberOfDays)

Post back with more details of exactly what you're trying to do.

 

[red skelton via AccessMonster.com]

Thanks for your reply,

Ultimately, I am using the query as a record source for a form. The bit I'm
doing now is to compile a reference number that includes a totoal of 14
characters, the first 7 being static which I am using "Static1" the next 3
characters is the Julian date (not including the year) which is what I'm
trying to do now, the next 2 characters are static "Static2" and the last two
characters will be a sequence number ie 01, 02 etc based on the first letter
sent out, second letter sent out etc.. that day. I am going to TRY and
concatenate the
components in the Query to provide a 14 character number that will
automatically fill in when the person opens the form. Hope this makes sense.
Again, thats for you assistance.

Red

Unfortunately, "Julian date" isn't really specific: there are several
definitions.

It's also not clear to me exactly what you want your code to do: the two
snippets you've posted do different things (and, for that matter, the second
one won't work, since you've got a literal text string "NumberofDays" where
you need a numeric value: if you've got a variable named NumberofDays,
remove the quotes from around it)

Assuming your definition of Julian date is the 4 digit year followed by a 3
digit number indicating which day of the year it is, you can generate that
as a string using:

Format(MyDateField, "yyyy") & Format(DatePart("y", MyDateField), "000")

If all you want is the day number, you can use Format(MyDateField, "y") to
get a string, or DatePart("y", MyDateField) to get a number.

To convert the 3 digit number back to the date, as mentioned above you need
to use

DateSerial(Year(Date()), 1, NumberOfDays)

Post back with more details of exactly what you're trying to do.

Hi again,

[quoted text clipped - 29 lines]

 

[Douglas J. Steele]

Did I give you enough to go on, then? If not, post back with specific
questions (samples of the data always help...)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your reply,

Ultimately, I am using the query as a record source for a form. The bit
I'm
doing now is to compile a reference number that includes a totoal of 14
characters, the first 7 being static which I am using "Static1" the next
3
characters is the Julian date (not including the year) which is what I'm
trying to do now, the next 2 characters are static "Static2" and the last
two
characters will be a sequence number ie 01, 02 etc based on the first
letter
sent out, second letter sent out etc.. that day. I am going to TRY and
concatenate the
components in the Query to provide a 14 character number that will
automatically fill in when the person opens the form. Hope this makes
sense.
Again, thats for you assistance.

Red

Unfortunately, "Julian date" isn't really specific: there are several
definitions.

It's also not clear to me exactly what you want your code to do: the two
snippets you've posted do different things (and, for that matter, the
second
one won't work, since you've got a literal text string "NumberofDays"
where
you need a numeric value: if you've got a variable named NumberofDays,
remove the quotes from around it)

Assuming your definition of Julian date is the 4 digit year followed by a
3
digit number indicating which day of the year it is, you can generate that
as a string using:

Format(MyDateField, "yyyy") & Format(DatePart("y", MyDateField), "000")

If all you want is the day number, you can use Format(MyDateField, "y") to
get a string, or DatePart("y", MyDateField) to get a number.

To convert the 3 digit number back to the date, as mentioned above you
need
to use

DateSerial(Year(Date()), 1, NumberOfDays)

Post back with more details of exactly what you're trying to do.

Hi again,

[quoted text clipped - 29 lines]

Red

 

[red skelton via AccessMonster.com]

Sorry for the long break, just got back to work after the New Year holiday.
To be honest with you, I haven't tried to do anymore since your last post as
the hoilday was upon us. I'll work on it today and let you know how it goes.
Thanks for your assistance.

Red

Did I give you enough to go on, then? If not, post back with specific
questions (samples of the data always help...)

Thanks for your reply,

[quoted text clipped - 51 lines]

 

[red skelton via AccessMonster.com]

Douglas J. Steele,

Sorry for the delay in replying, but your suggestion works fine and just
wanted to send my thanks.

Sorry for the long break, just got back to work after the New Year holiday.
To be honest with you, I haven't tried to do anymore since your last post as
the hoilday was upon us. I'll work on it today and let you know how it goes.
Thanks for your assistance.

Red

Did I give you enough to go on, then? If not, post back with specific
questions (samples of the data always help...)

[quoted text clipped - 4 lines]