ACC: Converting Julian Dates with Visual or Access Basic Code

Last reviewed: March 17, 1998
Article ID: Q92816
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

Most government agencies and contractors require the use of Julian dates. A Julian date starts with a two-digit year, and then counts the number of days from January 1. Microsoft Access does not have any built-in functions for using or converting Julian dates to standard dates. However, you can write a function to do this. This article demonstrates a sample user- defined function that converts a Julian date to a standard serial date. Note that the following sample code assumes the year is in the 20th century. The code must be changed to process dates after the year 2000.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

You can use the following sample function, ConvertJulian(), to convert Julian dates to standard dates.

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

  1. Create a module and type the following line in the Declarations section:

          Option Explicit
    

  2. Type the following procedure:

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

Examples of How to Use the ConvertJulian() Function

Example 1:

  1. Create a new form in Design view not based on any table or query.

  2. Add the following text box controls to the form:

          Text Box:
    
             Name: Text0
          Text Box:
             Name: Text2
             ControlSource: =ConvertJulian([Text0])
    
    

  3. View the form in Form view. Type 90001 in the first text box, and then press TAB to move to the second text box. Note that the Julian date in the first text box displays as a standard date in the second text box.

Example 2:

  1. Create and save the following new table called Table1:

          Table: Table1
          --------------------
          Field Name: Julian
    
             Data Type: Number
    
    

  2. View the table in Datasheet view. Add the following values to the table:

          90001
          90010
          92100
    

  3. Create a new query based on Table1 and drag the Julian field to the query grid.

  4. In the Field row of another column on the query grid, enter the following:

          Normal: ConvertJulian([Julian])
    

  5. Run the query. Note that the Julian dates are converted to standard dates.
Keywords          : kbprg kb2000
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 17, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.