Going from a Julian date to mm/dd/yyyy

[Bill Pugh]

This may be simple but I'm having a real issue with it. I've used

Format([Input Table]!Birth_Date,"yyyy") & Format(Format([Input Table]!Birth_Date,"y"),"000")

to go from a date to a julian but now I need to go from a 4 digit julian date to a mm/dd/yyyy.

For example ... Today is julian date 5356 "year 2005 and 356 days into the year" and I need to change this back to 12/22/2005.

How do you do this I would rather do it within the query than do any code.

 

[RuralGuy]

This is not very universal but maybe it will give you some ideas.
JDate = DateAdd("d", (JulianVal Mod 1000), DateSerial((JulianVal \ 1000) + 2000, 1, 0))

 

[raskew]

Hi -

You might also want to take a look at Pat Hartman's response in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54387

Bob

 

[Bill Pugh]

This is not very universal but maybe it will give you some ideas.
JDate = DateAdd("d", (JulianVal Mod 1000), DateSerial((JulianVal \ 1000) + 2000, 1, 0))

This would work but the 4 digit Julian field I need to change to a date is a text field. When I try this I get an error on mismatch type.

Thanks

 

[RuralGuy]

Look up Cint() in VBA help!
JDate = DateAdd("d", (CInt(JulianVal) Mod 1000), DateSerial(([/b]CInt(JulianVal)[/b] \ 1000) + 2000, 1, 0))