Julian Days (1 Viewer)

[SusanC]

Hi all!
I need some help with a few calculations in my database, but please bear with me as I'm not too crash hot on how this all works.

I have a field in a table with a julian day..i.e. 50.04 instead of 19th Feb 2004. I would like another field on the same table to plus 250 days to this number.

I have found a few codes that will convert julian days to 'normal' dates to make the calculation easier. I have also found code to find the difference between Julian days (which is something else I need to do!).

However, I'm unsure how to actually use the code, where to put it and if I'll need to put it in a query and update the main table.

Can anyone help me...please talk in simple terms though as most other forums I have tried seem to go straight over my head!!
thanks to anyone who replies!
cheers
Sue

 

[Mile-O]

What's the code?

 

[SusanC]

Hi this is the code to convert a julian day to a date

Function CJulian2Date(JulDay As Integer, Optional YYYY)
    If IsMissing(YYYY) Then YYYY = Year(Date)
    If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _
    > 9999 Then Exit Function
    If JulDay > 0 And JulDay < 366 Or JulDay = 366 And _
    YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then _
        CJulian2Date = DateSerial(YYYY, 1, JulDay)
End Function

I'm just not sure what to do with it and how it relates to the field with the 'day' in it.

I also have the code to convert the 'date' back again -

Option Compare Database
Option Explicit

Function ConvertJulian(JulianDate As Long)
         ConvertJulian = DateSerial(1900 + Int(JulianDate / 1000), _
                       1, JulianDate Mod 1000)
End Function

any ideas?
thanks for your help,
Sue

 

[Mile-O]

Firstly, if you have a field with the original date and you want to do a calculate then you don't need the extra field to store the calculation - it's a waste of time, resources, and a violation of the Third Normal Form (3NF) of database design.

The calculation should be calculated in an expression in a query, form, or report.

How is the data stored in your original field? As a three digit number, a five digit number, or a seven digit number?

 

[Mile-O]

Use this function also; it's from the same site as your CJulian2Date function.

You can delete the ConvertJulian function.

Function CDate2Julian(MyDate As Date) As String
        CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _
        31), "000")
End Function

I noticed you'd already answered that in your original post so the expression for a query would be (where Original is assumed to be your field name)

NewDate: CDate2Julian(DateAdd("d",250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)),CInt("20" & Mid([Original],InStr(1,[Original],".")+1))))) & "." & Format(DateAdd("d",250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)),CInt("20" & Mid([Original],InStr(1,[Original],".")+1)))),"yy")

 

[SusanC]

Hi, thanks for replying!

The date is in the form of a 5digit number...i.e 365.04.

I'll give those other codes a whirl. In the query do I choose the field with a date in and leave the next one blank and fill in the criteria with the code?
sorry - bit of a newbie!

 

[Mile-O]

I'll give those other codes a whirl. In the query do I choose the field with a date in and leave the next one blank and fill in the criteria with the code?

Yes.

 

[SusanC]

Hi I've tried that but it won't work. I don't really know how to adapt it. I followed exactly what you said.

If I enter code into an empty query field it won't work since the field is not entered? how can I get round that?

If I choose an empty field from the same table the code still doesn't work anyway!

I don't know what else to do?
Sue

 

[Mile-O]

Example

 

[SusanC]

oooooooo that's a bit clearer- thank you so much! I knew it should be easier!
you've saved me some time!
thanks
Sue

 

[SusanC]

Hi,
sorry just one more question!

Now that I have managed that I can update it to my table but is there a way to do this automatically so it updates as soon as you enter the date or when you open the table?

thanks again
Sue

 

[Mile-O]

Why are you putting it in a table?

It's better to use the expression as the ControlSource on a form if you want it to update automatically.

i.e. where Original is the name of the textbox

=CDate2Julian(DateAdd("d" ,250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)), CInt("20" & Mid([Original],InStr(1,[Original],".")+1))))) & "." & Format(DateAdd("d" ,250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)),CInt("20" & Mid([Original],InStr(1,[Original],".")+1)))),"yy")

 

[SusanC]

Hi,
I've managed to do that which is great!
Can I get rid of the Error message that appears before the info is entered?
Can I then store this calculated field? I've seen loads of sites where people don't think you should store them...why?
thanks
Sue

 

[Mile-O]

Can I get rid of the Error message that appears before the info is entered?

Yes.

=IIf(IsNull([Original]), "-", CDate2Julian(DateAdd("d" ,250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)), CInt("20" & Mid([Original],InStr(1,[Original],".")+1))))) & "." & Format(DateAdd("d" ,250,CJulian2Date(CInt(Left([Original],InStr(1,[Original],".")-1)),CInt("20" & Mid([Original],InStr(1,[Original],".")+1)))),"yy"))

Can I then store this calculated field? I've seen loads of sites where people don't think you should store them...why?

And everyone on this site will also say "don't!"

Firstly as I said earlier, storing calculated values is a violation of Third Normal ormn (3NF) which states that there should be no "non-key dependancies" within a table.

What this means is that no field within a table should depend on another field unless, of course, that field is the table's primary key.

By depend, usually, we mean be the result of a calculation. For example we have three fields called Price, Quantity, and Total. Total is arrived at by Price multiplied by Quantity. We can calculate the Total at any time on a report, form, or in a query. If we were to store the Total in a field then there is the chance that the price or quantity could be changed; the Total then would be incorrect. That's why it's always better to calculate at runtime. It also has the added benefit of reducing storage space.

In your example, you have the date to begin with and you only want a date that's 250 days ahead of that date. That implies that the second date depends on the first. As you have an expression which will calculate the second date you can use this in forms, queries, and reports to meet your needs and you'll save the space of one field within your database.

I'll fix that long expression into a function to make it easier to refer to.

 

[raskew]

Hi Susan-

Have been watching this thing painfully unfold. Gotta throw in a couple of questions/comments:

1) What in the scenario makes it necessary to use this arcane date
handling (e.g. 50.2004) method rather than normal date/time data types?
Wouldn't it be more to the point to store the date normally and then, if
the 'julian date' is necessary a calculated field using Mileo's code is all that's needed.

2) Does your table already include a date field in date/time data type?

Best Wishes, Bob

 

[SusanC]

Hi Susan-

Have been watching this thing painfully unfold. Gotta throw in a couple of questions/comments:

1) What in the scenario makes it necessary to use this arcane date
handling (e.g. 50.2004) method rather than normal date/time data types?
Wouldn't it be more to the point to store the date normally and then, if
the 'julian date' is necessary a calculated field using Mileo's code is all that's needed.

2) Does your table already include a date field in date/time data type?

Best Wishes, Bob

Hi Bob,
I don't think it's been too painful! I've had worse..tee hee..
anyway, as to your questions...I'm using the Julian dates as it really is easier this way. That's what we use to work with and so putting in 'normal' dates just to then get them calculated into Julian dates is a bit unecessary but I can see your point. As such, the date field is set to text as it is easier than changing it to fit the julian date.

thanks
Sue

 

[Navyguy]

Just out of interest, most of the worlds' militaries use the Julian date format. Wouldn't want to be invading a country on the 2 Jan when everbody else is showing up on the 1st of Feb!!!

Navyguy

 

[SusanC]

Just out of interest, most of the worlds' militaries use the Julian date format. Wouldn't want to be invading a country on the 2 Jan when everbody else is showing up on the 1st of Feb!!!

Navyguy

We seem to have done okay so far!

 

[phamyh]

Do you know a better way to convert Julian dates to include where the data is 0 or null?

Thanks