 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / October 2007Date to Julian date format
I need to be able to take a date format (mm/dd/yy) entered on an Access form and convert it to a Julian date for an AS400 query. The file on the AS400 is looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288. Can anyone help with this? I am using Access 2003. I'm not quite sure about the "1C" at the front, since your example only has a 1. To get 07288 from 15 October, 2007, you can use the Format function with an argument of "yyy": ?Format(#2007-10-15#, "yyy") 07288  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I need to be able to take a date format (mm/dd/yy) entered on an Access >form [quoted text clipped - 3 lines] > > Can anyone help with this? I am using Access 2003. >I need to be able to take a date format (mm/dd/yy) entered on an Access >form [quoted text clipped - 3 lines] > > Can anyone help with this? I am using Access 2003. Paste this function into a standard module: Public Function DateToJulian(NormalDate As Date) As String Dim dYear As String Dim jDay As String dYear = Format(NormalDate, "yy") jDay = Format(Str(NormalDate - DateValue("1/1/" & Str(dYear)) + 1), "000") DateToJulian = "1" & dYear & jDay End Function Then use it like this: Debug.Print DateToJulian("10/15/07") Result: 107288 >I need to be able to take a date format (mm/dd/yy) entered on an Access form >and convert it to a Julian date for an AS400 query. The file on the AS400 is >looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288. Format(dt, "\1yyy") SignatureMarsh MVP [MS Access] Thanks everyone for your response. I was able to use Marshall's post to get it to work. > >I need to be able to take a date format (mm/dd/yy) entered on an Access form > >and convert it to a Julian date for an AS400 query. The file on the AS400 is > >looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288. > > Format(dt, "\1yyy") Glad to hear that it worked, but Doug said essentially the same thing. SignatureMarsh MVP [MS Access] >Thanks everyone for your response. I was able to use Marshall's post to get >it to work. [quoted text clipped - 4 lines] >> >> Format(dt, "\1yyy") OK. Glad Marshall's version (and Doug's) worked for you. BUT my test using Jan 2, 2007 return 072 and not 07002. '==================================================== John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== > Thanks everyone for your response. I was able to use Marshall's post to get > it to work. [quoted text clipped - 4 lines] >> >> Format(dt, "\1yyy") >OK. Glad Marshall's version (and Doug's) worked for you. BUT my test >using Jan 2, 2007 return 072 and not 07002. Arrgghh, I never checked if the y format code returned leading zeros. So much for quick answers :-( Stuart, use John's expression if you want Jan 2 to come out as 07002. SignatureMarsh MVP [MS Access] >>OK. Glad Marshall's version (and Doug's) worked for you. BUT my test >>using Jan 2, 2007 return 072 and not 07002. [quoted text clipped - 4 lines] > Stuart, use John's expression if you want Jan 2 to come out > as 07002. Arrgghh again. I've never had a need for julians myself, so I wrote the function there and then (it looked simple enough). So much for ad-hoc functions without thorough testing :-( Thanks The other solutions posted will fail if the date occurs in the first 99 days of the year. Format(YourDate,"YY") & Format(DatePart("y",YourDate),"000") Assuming that the 21st Century is 1 and the 20th Century is 0 C = Year(Date)\100 -19 will return the century so Year(YourDate)\100 -19 & Format(YourDate,"YY") & Format(DatePart("y",YourDate),"000") SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . >I need to be able to take a date format (mm/dd/yy) entered on an Access >form [quoted text clipped - 3 lines] > > Can anyone help with this? I am using Access 2003. Party pooper! <g> SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > The other solutions posted will fail if the date occurs in the first 99 > days of the year. [quoted text clipped - 14 lines] >> >> Can anyone help with this? I am using Access 2003. AH HA! Now I know why two MVP's made this error. You shouldn't be partying while trying to answer the question - a little beer or wine can definitely change the accuracy of the answers. Of course, sometimes a little beer or wine will generate a more creative solution. SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Party pooper! <g> > [quoted text clipped - 17 lines] >>> >>> Can anyone help with this? I am using Access 2003. >AH HA! Now I know why two MVP's made this error. > >You shouldn't be partying while trying to answer the question - a little >beer or wine can definitely change the accuracy of the answers. Of course, >sometimes a little beer or wine will generate a more creative solution. And here I thought is was just a lack of sleep that was responsible. Thanks for straightening me out on this subtle issue, John. -- Marsh P.S. Do you have an empirically derived graph of the Bugs per Beer ratio? > P.S. Do you have an empirically derived graph of the Bugs > per Beer ratio? http://xkcd.com/323/ SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.