Help Convert Julian Date to regular date

[toadmaster]

I am new to access and I am learning as I go along; I am attempting to convert Julian dates to regular dates "MM-DD-YY"; I have tried the code below in the Query screen under criteria.

CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your Field Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

Trouble is it's not bringing up any result; the database field it's been applied to is blank now?

I am not even sure if I am going about it the right way

[Delerna]

There may be a function that will convert it for you but I don't know it.

So
The format that I am familiar with for Julian date is 4 digits the first 3 is the day number of the year and the last is the last digit in the year. Therefore you must know the decade that the date falls within.

So to convert it yourself you need to calculate the year
I will assume the date is in the decade 01/01/2000 - 31/12/2009
which means the decade start year is 2000
I also assume the Julian date is of type string

so the year is
2000 + cint(right(JulianDte,1))
and the date is 01/01/CalculatedYear + (The3DigitJulianDay-1)


The complete statement would be something like

cdate("01/01/" & cstr(2000 + cint(right(JulianDte,1)) )) + cint(left(JulianDte,3)-1)

Where JulianDate is the field name of the Julian Date field
and of course you can add the date format bit in if you want

I hope I got enough brackets there and I hope it helps

[OldBirdman]

Widipedia informs that Julian Date, as used in Computer Science is Ordinal Date, a date as yyyy-ddd where ddd is the day number for the year, with values from 1 to 365 (366 if leap year).

After verifying that yyyy is a numeric value (probably between 1900 and Now() ) and that ddd is numeric and 1-366, then the date is

dteYourDateVariable = DateAdd("d", Mid(strJulianDate, 6), "12/31/" & Left(strJulianDate, 4) - 1)

[toadmaster]

Thank you Delerna and OldBirdMan for the prompt reply.

The following are samples of the dates I am attempting to convert

81105
120505
102901
71894

The field name is [TOBIDE]; the Data Type is "Number" and the field size is set to "Decimal" (I have tried to change the data type to Date/Time - Keep getting error messages saying it's run of disk space) everytime I run the query below

CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([TOBIDE]/1000))))+[TOBIDE]-Int([TOBIDE]/1000)*1000))-1,"mm-dd-yy"))

It just falls over does not bring any results in the table; but once I take the query out from the Criteria it returns the julian dates listed above???

I need to get these dates into "MM-DD-YY"

[OldBirdman]

These are neither true Julian Dates, where today would be 2455057, or Ordinal Dates, where today would be 2009-226. They are not as Delerna thought, days since the start of the decade. Subtracting 120505 from today's date gives a year of 1570, not a probable start date for any counting system.
The best guess is that these numbers are the date & time where the time is the last 3 digits. The remaining digits: 81, 120, 102, 71 would all then be number of days in year, but which year?

Rereading the original post, I am completely confused.
1) Where are these numbers? In a table, I assume because

The field name is [TOBIDE]; the Data Type is "Number" and the field size is set to "Decimal"

that field is a field in a table, and not a control on a form.

2)The criteria stated seems to be copied from an example without any adjustments for this project.

I have tried the code below in the Query screen under criteria.
CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your Field Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

What is [Your Field Name] in this context?

3)A criteria is to test against a field. What is in the field being tested against. WHERE [Value]=7 tests the field "Value" against the number 7, but what is this criteria testing against.

4)

I am attempting to convert Julian dates to regular dates "MM-DD-YY";

and put the answer where?

5)

Trouble is it's not bringing up any result; the database field it's been applied to is blank now?

What is a database field? What & where is this 'blank'?

This question needs to be clarified before any real help can be given. Without information such as how to manually interpret the numbers given as dates, I don't see an answer. As far as the query goes, more explanation is needed.

[toadmaster]

I appreciate your help OldBirdman; this is the situation:

These numbers are being displayed in a table called MKF22(table name) under TOBIDE(field name)

81105
120505
102901
71894

I am trying to extract the date and time from the numbers above and to display them as "MM/DD/YY" in the TOBIDE(field name) under the (MKF22) table instead of the current form that is being displayed.

Eg:

Currently 81105 is what is being displayed would like it to show 08/11/2005 (if that is the correct date format)? I hope I am making sense????????? As I said I am very new to access.

Thank you!!!!!!!!!!!

[Delerna]

So those are simply the date with the /'s removed in
either "mmddyy" or "mddyy" format


81105=08/11/05
120505=12/05/05
101201=10/29/01
71894=07/18/94

If that is the case then something like this might work

cdate(
   iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(TOBIDE),2)
   & "/" & 
   left(right(cstr(TOBIDE),4),2) 
   & "/" & 
   right(cstr(TOBIDE),2)
)
 

[Delerna]

Incidentally, I thought I would try plugging one of the values into your formula
and calculate it out by hand to try and see why you were getting the error.

I didn't discover the reason for the error but the result is interesting

CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([TOBIDE]/1000))))+[TOBIDE]-Int([TOBIDE]/1000)*1000))-1,"mm-dd-yy"))
CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int( 120505/ 1000))))+ 120505 -Int( 120505 /1000)*1000))-1,"mm-dd-yy"))
CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+        120       )))+ 120505 -        120       *1000))-1,"mm-dd-yy"))
CDate(Format(((DateValue("01/01/"+LTrim(Str(        2020           )))+ 120505 -           120000      ))-1,"mm-dd-yy"))
CDate(Format(((DateValue("01/01/"+          "2020"                   )+            505                 ))-1,"mm-dd-yy"))
CDate(Format(((DateValue("01/01/2020"                                )+            505                 ))-1,"mm-dd-yy"))
CDate(Format(( 05/20/2021)-1                                                                               ,"mm-dd-yy"))
CDate(Format(05/19/2021,"mm-dd-yy")
 

Assuming I havent made a mistake, the answer 05/19/2021
is nothing like the result 12/05/05
you are after.

When I have difficulty with large unwieldly formulas I sometimes do this to help me spot why its not working.

More usually I tend to build the formula up one piece at a time as separate fields before combining them into the final formula. That way I can see which part of the formula is going wrong as I write it.


Going from memory here, most of my queries are written on SQL server these days,
but I don't think you can do this
"01/01/" + "2020" in access can you?
Dosn't it have to be "01/01/" & "2020" ?

[toadmaster]

Delerna you are genius; expert doesn't even begin to describe your abilities.

Works like a charm; the thing is I could never figure out why I kept getting 05/19/2021
instead of 12/05/05.

I got those same results even with simple formating like

Expr1: Format:([TOBIDE], "mm/dd/yy")

Even though the date should already be in "mmddyy" format.

Thank you and OldBirdman!!!!!!!!!!! YOU BOTH ROCKKKKKKKKKKKKKKKKKK

[Delerna]

I can't get my head out the door now :)

Seriously though, every expert (whether they have that word against their name or not) in every topic here ....... You ROCKKKKKKKKK.

[OldBirdman]

To simplify the issue, these numbers can be converted to Access dates by

CDate(Format(120505,"&&/&&/&&")) 

[Delerna]

That's much better than my clunky method

[toadmaster]

Quick question? I attempting to include an 'OR' condition to handle fields that are blank or have a '0' value.

I have tried both of the following statements and non of them seem to do it?

Eg 1 :
OR IIf(([TOBIDE])= "", [TOBIDE])

Eg 2:

OR IIf(Len([TOBIDE])= 1, [TOBIDE])

the entire statement in my query goes like this

cdate(
iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(T OBIDE),2)
& "/" &
left(right(cstr(TOBIDE),4),2)
& "/" &
right(cstr(TOBIDE),2) OR IIf([TOBIDE]) = "",([TOBIDE])
)

The entire OR section is being ignored when I run the query...........

[toadmaster]

I have figured out a way to get the blank field to display "0" as against the following data 12:00:00 AM or 12/29/1899 which it was giving me earlier by using the following query(tips from Delerna)

IIf(Len([TOBIDE])=1,0)

but the problem right now is combining it with the previous IIf statement; I keep getting #Error or it totally ignores the query altogether???? Below is the query

cdate(
iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(T OBIDE),2)
& "/" &
left(right(cstr(TOBIDE),4),2)
& "/" &
right(cstr(TOBIDE),2), IIf(Len([TOBIDE])=1,0))

I have tried using OR in there to handle the second IIf function and it does not work????????????? Any Suggestions????????????

[Delerna]

Blank fields can be handled with NZ.
In a query it is used something like this

SELECT NZ([TheField],TheValueToReturnIf[TheField]IsNull) as FieldName
 

With the method I gave you, you would wrap my if in the false part of another if. (Its called a Nested IIF)

so
IIF(len(NZ(TOBIDE,0))=1,null,MyClunkyIIF)

You finish up with an iff within an iif like this

iif(len(NZ(TOBIDE,0))=1,null,
   cdate
   (   iif(len(TOBIDE)=5,left(cstr(TOBIDE),1),left(cstr(TOBIDE),2) 
       & "/" &  
       left(right(cstr(TOBIDE),4),2)  
       & "/" &  
       right(cstr(TOBIDE),2) 
   ) 
)
 

HOWEVER
Using OldBirdman's much neater suggestion you could do something like this

 
IIF(len(NZ(TOBIDE,0))=1,null,CDate(Format(TOBIDE,"&&/&&/&&")))
 
 

[toadmaster]

YOU Guys did it ones again; thank you so much. The more I discover the more interesting Access gets!!!!!!!!!!!!!!!! Awesome