Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: How to make value from function public?

    I have a public function CalculateJulianDate:
    Code:
    Option Compare Database
    Option Explicit
    
    
    
    'This procedure takes a serial date number entered in an Input Box
    'and converts it to the appropriate Julian date and returns it as a
    'string.
    Public Function CalculateJulianDate()
        Dim SerialDate As Date      'The serial date.
        Dim SerialYear As String    'The year of the serial date.
        Dim JulianYear As String    'Serial year -1 digit.
        Dim JulianDay As String
        Dim JulianDate As String    'The converted Julian date value
    
        'Prompt for input and assign the value entered to the
        'SerialDate variable.
        SerialDate = Date
    
        'Assign SerialYear the year number
        SerialYear = Format(SerialDate, "yy", "0")
        
        'Convert to JulianYear
        JulianYear = Right(SerialYear, 1)
    
        'Find the day number for SerialDate
        JulianDay = Format(Str(SerialDate - DateValue("1/1/" & _
        Str(SerialYear)) + 1), "000")
        JulianDate = JulianYear & JulianDay
    
    End Function
    How do I make the value of JulianDate available anywhere in the database? IE if I want to fill a control with this value, I would call CalculateJulianDate and then declare the value of the control as JulianDate. I've tried doing it in this simple method, but it doesn't work. I tried Dim'ing JulianDate as a string in a seperate function, in hopes that calling the function would set the value of JulianDate in both functions, but that didn't work. This should be a simple fix, I just don't know how to do it. I don't want to declare the values of the controls INSIDE CalculateJulianDate, because I want to be able to use JulianDate in several places within my db.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I would create a new module and paste the function as such in the module:

    Function CalculateJulianDate(SerialDate as variant) as variant '(note: no need to put 'Public Function')
    ' Dim SerialDate As Date 'The serial date. (this value is passed to the function)
    Dim SerialYear As String 'The year of the serial date.
    Dim JulianYear As String 'Serial year -1 digit.
    Dim JulianDay As String
    ' Dim JulianDate As String 'The converted Julian date value (the function will return this value)

    'Prompt for input and assign the value entered to the
    'SerialDate variable.
    ' SerialDate = Date (date value is passed TO the function)

    'Assign SerialYear the year number
    SerialYear = Format(SerialDate, "yy", "0")

    'Convert to JulianYear
    JulianYear = Right(SerialYear, 1)

    'Find the day number for SerialDate
    JulianDay = Format(Str(SerialDate - DateValue("1/1/" & _
    Str(SerialYear)) + 1), "000")

    CalculateJulianDate = JulianYear & JulianDay

    ' JulianDate = JulianYear & JulianDay (no need for this since the above line will return the value to the code calling the function)

    End Function

    And then you would call the function (anywhere in code or in any expressions (ie. query or unbound field)) as such:

    me!MyJulianDateFieldName = CalculateJulianDate(me!SomeDateFieldToConvert)
    or
    me!MyJulianDateFieldName = CalculateJulianDate(Date())

    or in a query, simply add it as an expression:
    ex:
    MyExpression: CalculateJulianDate([SomeDateFieldtoConvert])
    or
    MyExpression: CalculateJulianDate(Date())

    or for the control source of an unbound field on the form:
    =CalculateJulianDate(me!SomeDateFieldtoConvert)

    Note also: Putting these types of functions in a module is a great way to calculate (or return) values from anywhere. You simply pass the value (or values) to the function and it returns the calculated (or other type) value.

    ex:
    Function retCustomersFullName(vCustomerID as variant) as variant
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & vCustomerID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    retCustomersFullName = rs!FirstName & " " & rs!LastName
    rs.close
    set rs = nothing
    End Function

    or another example:
    Function retSumOf2Values(intValue1 as integer, intValue2 as integer) as integer
    retSumOf2Values = intValue1 + intValue2
    end Function


    Also note: it doesn't matter what you name the module but if you're going to write a lot of these types of functions in modules, I would create separate modules for different types of functions (ie. General Functions Module, Customer Function Module, etc.. or Returning value type Functions, Writing Data Functions, etc..).

    I do this quite often in all my applications. Functions in modules is an easy way to do things which otherwise are difficult to do. And the great thing about modules is that you can simply import the module from one mdb into another and cut your development time down dramatically!
    Last edited by pkstormy; 04-09-10 at 15:22.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    before I make the same mistake i've made a couple of times already: what does it mean when you say the serialdate value is passed to the function?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I used SerialDate as the variable name to illustrate how you can simply pass 'any' type of value to the function. In your code, you set SerialDate = Date() in the code itself. Instead of doing this, you can make the function 're-usable' by declaring a variable (which can be named anything) to be passed 'into' the Function to use within the calculations of that function...
    ex:
    Function CalculateJulianDate(AnyDateValueVariable as Date) as variant

    Thus when calling the function, you're not limited to the function always just calculating off Date() but instead, you could return the Julian Date for 'any' date value passed to the function...
    ex:

    me!MyJulianDateFieldName = CalculateJulianDate(#03/22/2010#)

    or

    me!MyJulianDateFieldName = CalculateJulianDate(#04/2/2010#)

    or (if you want to calculate the Julain Date for just today's date)...

    me!MyJulianDateFieldName = CalculateJulianDate(Date())

    When you're designing Functions in modules, you don't really want to limit it to 'specific' values but instead have the flexibility to pass (to the function), any type of value to then utilize within your calculations for that function.

    If your function was as such:
    Function CalculateJulianDate() as variant
    CalculateJulianDate = Date() + some other calculation...
    End function

    You'd then only ever be able to return the Julian date for today's date when calling the function (which you may or may not want to do). Again, flexibility to pass 'any' date to the function may or may not be needed but to me, it usually makes sense to keep the option open to pass any value to the function in which to calculate off of.

    I hope that makes sense.
    Last edited by pkstormy; 04-09-10 at 16:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    oh i see. that's interesting. Thanks!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're only (and always) just calculating the Julian Date off of today's date, you may want to take a different approach (which others might be able to better help you with but it involves declaring a public/global type variable as a calculation - again, I usually don't do this so others might have a good example as I can't recall the specifics on how this is done).
    Last edited by pkstormy; 04-09-10 at 16:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I don't know why. What you provided me works perfectly fine for calculating either today's date or a given date. i've tested it calling the function like this:

    ?CalculateJulianDate(Date)

    and it gives me today's Julian date, which by the way is 0099. In most civilian julian date formats, however, it would be 10099. I modified it to use the 4 digit julian date that we use in the military.

    I don't see anything wrong with your suggestion. It works, and is flexible. I like it.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good to know. Thanks for the reply and I'm glad the advice was helpful.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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