Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: How do I display module results in a form text box

    Hello all,
    I am still learning Access and need some assistance. I have the following code (currently in a module that is supposed to be called after a change is made to the form) to convert a calendar date to a julian date:

    Code:
    Option Explicit
    ' *********************************************************************
    ' FUNCTION: CDate2Julian()
    '
    ' PURPOSE: Convert a date to a Julian day. The function works with
    '          dates based on the Gregorian (modern) calendar.
    '
    ' ARGUMENTS:
    '    MyDate: A valid Microsoft Access date.
    '
    ' RETURNS: A three digit Julian day as a string.
    ' *********************************************************************
    
    Function CDate2Julian(MyDate As Date) As String
    
       CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _
         31), "000")
         
        'following formula returns 4-digit julian date for current date like 9223 for 8-11-2009 in immediate mode (remove the comment)
    
        ' ?Year(DATE()) Mod 100 & CDate2Julian(DATE())
         
    
    End Function
    I need to display the results seen in the immediate window to a text box on a form and eventually saved in a table. I have tried placing
    Code:
    ?Year(DATE()) Mod 100 & CDate2Julian(DATE())
    directly in the text box, in a query, in the table cell and probably some other places as well with no success.

    Any help would be appreciated.

    Thanks,
    Andrew

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    There are many ways to call a function. You could try the following:

    On AfterUpdate of Date field have code like this:

    Me!ResultField= CDate2Julian(Me!DateField)
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    May 2009
    Posts
    48
    Thanks SoftwareMatters.

    The form code is below with comments indicating the name of the module and what should be the logical flow of the code.

    Code:
    Private Sub JulianDate_AfterUpdate()
    Call JulianDateConvert
    
    'Call JulianDateConvert calls a module with the following code
    
    ''Function CDate2Julian(MyDate As Date) As String
    
     ''  CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, 31), "000")
         
        ''following formula returns 4-digit julian date for current date like 9223 for 8-11-2009
        
        ''?Year(DATE()) Mod 100 & CDate2Julian(DATE())
    
    ''End Function
    
    'When the JulianDateConvert module ends, control should be returned to the following code
    
    'RequestForm is the name of the form, JulianDate is the name of the text box  to display the julian date,
    'and Req Date is the date entered on the form by the user
    
        Forms!RequestForm!JulianDate = CDate2Julian(Forms!RequestForm![Req date])
        
    End Sub
    But where does
    Code:
    ?Year(DATE()) Mod 100 & CDate2Julian(DATE())
    go so and how do the results get displayed on the form "RequestForm" in the text box "JulianDate"?

    Thanks,
    Andrew

  4. #4
    Join Date
    May 2009
    Posts
    48
    I have played around with many variations and this what I have come up with so far. The below code as you can see is a function and I have it in a module called JulianDateConvert since I have not been able to combine it with the Sub (probably cant combine them).

    Code:
    Public Function CDate2Julian(ReqDate As Date) As String
    
       CDate2Julian = Format(ReqDate - DateSerial(Year(ReqDate) - 1, 12, 31), "000")
      
    End Function
    The below code should call the function 'CDate2Julian' above, retrieve the user entered date from the text box called ReqDate and place the converted date into the JulianDate text box.

    Code:
    Private Sub JulianDate_AfterUpdate()
       
    'RequestForm is the name of the form, JulianDate is the name of the cell to display the julian date, 'and ReqDate is the date entered on the form by the user
    
    Forms!RequestForm!JulianDate = CDate2Julian(Year(Forms!RequestForm!ReqDate)) Mod 10 & CDate2Julian(Forms!RequestForm!ReqDate)
    
    End Sub
    Testing
    Code:
    Print Year(DATE()) Mod 100 & CDate2Julian(DATE())
    in immediate mode returns the correct date for 8-11-2009 which is 9223.

    Using
    Code:
    Forms!RequestForm!JulianDate = CDate2Julian(Year(Forms!RequestForm!ReqDate)) Mod 10 & CDate2Julian(Forms!RequestForm!ReqDate)
    in immediate mode for the same date above displays 2223 in immediate mode in which the first digit should be a 9.

    Can someone please assist me in getting the correct julian date to display on the form either after the date is entered on the form or when a button is pressed.

    Thanks,
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •