Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Location
    los angeles
    Posts
    10

    Question Unanswered: call function from query

    I created an Access module(function) that I can't seem to access from a query. In one of my queries I have the following column:

    TP_TIME: convertGMT_PT(GMT_TIME)

    When I try to run the query, I get the error message:

    Undefined function 'convertGMT_PT' in expression

    I've made sure that the function is public and I'm not sure what else to try. (Access is not one of my areas of strength)... Any help you can provide would be much appreciated.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the function in a module in the same database? If so the only thing I can think of that will prevent it from working is a typo.

  3. #3
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    Have you tried TP_TIME: convertGMT_PT([GMT_TIME]) ?
    I assume GMT_TIME is a field name. Access may want the [] around your field name.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    an access query will happily call a public function in a global module.

    summit else is wrong.

    maybe we should see the function


    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Location
    los angeles
    Posts
    10
    Thanks for the replies so far... Here are my answers:

    DCKunkle: Yes, the function is a module in the same database.

    ray705: I tried TP_TIME: convertGMT_PT([GMT_TIME]) and am getting the same error message

    izyrider: Since I am new to Access, it's quite possibly something else. Here is the module:

    Function convertGMT_PT(gmtDate As Date) As Date
    Dim calcYR As String
    Dim gmtBeg As Date
    Dim gmtEnd As Date
    Dim ptDate As Date
    Dim x As Integer

    calcYR = Year(gmtDate)
    'retrieve begin date of GMT Daylight Savings Time for the submitted date's Year
    For x = 1 To 7
    If (Day(DateValue("4/" & x & "/" & calcYR))) = 1 Then
    gmtBeg = DateValue(DateValue("4/" & x & "/" & calcYR) & TimeValue("10:00:00"))
    End If
    Next x

    'retrieve end date of GMT Daylight Savings Time for the submitted date's Year
    For x = 31 To 25 Step -1
    If (Day(DateValue("10/" & x & "/" & calcYR))) = 1 Then
    gmtEnd = DateValue(DateValue("10/" & x & "/" & calcYR) & TimeValue("10:00:00"))
    End If
    Next x

    If gmtDate >= gmtBeg And gmtDate <= gmtEnd Then
    'convert GMT submitted date to Pacific Daylight Savings Time
    If Hour(gmtDate) >= 7 Then
    ptDate = DateValue(DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) & TimeValue(Format(Hour(gmtDate) - 7, "00") & ":" & Format(Minute(gmtDate) - 7, "00") & ":" & Format(Second(gmtDate) - 7, "00")))
    Else
    ptDate = DateValue(DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) - 1 & TimeValue(Format(Hour(gmtDate) + 17, "00") & ":" & Format(Minute(gmtDate) - 7, "00") & ":" & Format(Second(gmtDate) - 7, "00")))
    End If
    Else
    'convert GMT submitted date to Pacific Standard Time
    If Hour(gmtDate) >= 8 Then
    ptDate = DateValue(DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) & TimeValue(Format(Hour(gmtDate) - 8, "00") & ":" & Format(Minute(gmtDate) - 7, "00") & ":" & Format(Second(gmtDate) - 7, "00")))
    Else
    ptDate = DateValue(DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) - 1 & TimeValue(Format(Hour(gmtDate) + 16, "00") & ":" & Format(Minute(gmtDate) - 7, "00") & ":" & Format(Second(gmtDate) - 7, "00")))
    End If
    End If
    'assign converted date value to returning value
    convertGMT_PT = ptDate
    End Function


    'Hope that's enough information... Any help would be greatly appreciated. Thanks in advance!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Public Function convertGMT_PT(gmtDate As Date) As Date


    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Mar 2004
    Location
    los angeles
    Posts
    10
    tried that. still getting the same error.

  8. #8
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    I do not have any problem with the function being called from a query but I do get some type mismatch errors in the code.

    One thing I noticed is that the wrong function for finding Sunday.
    If (Day(DateValue("4/" & x & "/" & calcYR))) = 1 Then
    Should be:
    If (WeekDay(DateValue("4/" & x & "/" & calcYR))) = 1 Then

    Still trying the rest of the code.

  9. #9
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    Are you sure you want to subtract 7 from the minutes and seconds?
    This is giving me negative numbers and thus the type mismatch.

  10. #10
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    The DateValue is redundant in your calculations. What ends up happening is that the second DateValue strips the time off of the result and you end up with just the date.

    ptDate = DateValue(DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) - 1 & TimeValue(Format(Hour(gmtDate) + 16, "00") & ":" & Format(Minute(gmtDate), "00") & ":" & Format(Second(gmtDate), "00")))
    should be
    ptDate = DateValue(Month(gmtDate) & "/" & Day(gmtDate) & "/" & Year(gmtDate)) - 1 & TimeValue(Format(Hour(gmtDate) + 16, "00") & ":" & Format(Minute(gmtDate), "00") & ":" & Format(Second(gmtDate), "00"))

    With this last change everything works fine - assuming your adding and subracting of hours is the proper adjustments.

  11. #11
    Join Date
    Apr 2004
    Posts
    7
    Have you saved the module that contains the function? Access will use the last saved version.

  12. #12
    Join Date
    Mar 2004
    Location
    los angeles
    Posts
    10

    THANKS!

    thanks for the tips. i actually already got an answer from my coworker (I didn't know it had to be stored in a module called Main)... As far as the logic errors, I worked it out, too so that it subtracts 7/24 or 8/24 from the date instead of splitting it up. Thanks for all the feedback!

  13. #13
    Join Date
    Apr 2004
    Posts
    7

    Re: THANKS!

    Originally posted by karizmatic
    thanks for the tips. i actually already got an answer from my coworker (I didn't know it had to be stored in a module called Main)... As far as the logic errors, I worked it out, too so that it subtracts 7/24 or 8/24 from the date instead of splitting it up. Thanks for all the feedback!
    Actually, I save functions in modules with various names, not just Main. If you learned something about special properties of a "Main" module I'd be interested to hear it.

    Anyway, glad you got you problem cleared up.

    -Davie

  14. #14
    Join Date
    Mar 2004
    Location
    los angeles
    Posts
    10
    Davie - i wish I could give you more information about the Main module (it would actually help me figure out WHY it will only call from that), but the person who helped me gave me that answer without much explanation for it either....
    but, now that you've mentioned it... How would I get the module to work from a different name without getting the original error I was getting?

    One more thing, in case someone was curious what the function ended up looking like:

    Public Function GMTtoPT(gmtDate As Date) As Date
    Dim calcYR As String
    Dim gmtBeg As Date
    Dim gmtEnd As Date
    Dim ptDate As Date
    Dim x As Integer

    calcYR = Year(gmtDate)
    'retrieve begin date of GMT Daylight Savings Time for the submitted date's Year
    For x = 1 To 7
    If (Day(DateValue("4/" & x & "/" & calcYR))) = 1 Then
    gmtBeg = (DateValue("4/" & x & "/" & calcYR) & " " & TimeValue("10:00:00"))
    End If
    Next x

    'retrieve end date of GMT Daylight Savings Time for the submitted date's Year
    For x = 31 To 25 Step -1
    If (Day(DateValue("10/" & x & "/" & calcYR))) = 1 Then
    gmtEnd = (DateValue("10/" & x & "/" & calcYR) & " " & TimeValue("09:00:00"))
    End If
    Next x

    If gmtDate >= gmtBeg And gmtDate <= gmtEnd Then
    'convert GMT submitted date to Pacific Daylight Savings Time
    ptDate = gmtDate - (7 / 24)
    Else
    'convert GMT submitted date to Pacific Standard Time
    ptDate = gmtDate - (8 / 24)
    End If
    'assign converted date value to returning value
    GMTtoPT = ptDate
    End Function


    I'll happilly accept any tips on getting it even more compact.

    Thanks!

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: THANKS!

    Don't know about module names, but I read that if you create a procedure called Main and put it in a module called AutoExec in Word, it runs on launch. Couldn't get it to work in Access, alas.

Posting Permissions

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