Julian Date Display in Query

[Eric H.]

Based on a date field elsewhere in the query, how do I get a query to display
a three digit format of the day (Julian Date), i.e., 005, 026, 154, etc,
where there are always 3 digits -- those below 100 would have one zero in
front, those below 10 would have two zeros?

 

[Ken Sheridan]

Format(DatePart("y",[TheDate]),"000")

will return the day of the year as three digits. The full 'Julian' Date
(aka ordinal date), incorporating the year will be returned with:

Year([TheDate]) & Format(DatePart("y",[TheDate]),"000")

Ken Sheridan
Stafford, England

 

[Eric H.]

Ken,

That was a truly invaluable tip! I needed that to get sequential numbers
issued to my records without changing the amount of characters in the field.

Thanks a TON!

 

[John W. Vinson]

That was a truly invaluable tip! I needed that to get sequential numbers
issued to my records without changing the amount of characters in the field.

Well... using the "julian date" might work IF you can be absolutely certain
that you'll never have two records on the same day, or need to track jobs in
two different years. That ice might be a bit thin out there this time of year
<g>...

It's easy to assign sequential numbers without any date referent by using
NZ(DMax("[numberfield]", "[tablename]")) + 1.