Using My Own Function in the Expression Builder

[stringman]

Hello,

I am trying to use a function I wrote to convert latitude and longitude from decimal to degrees/minutes/seconds (DMS). The function shows up in the Expression Builder under the "Built-in Functions" folder. The data is stored as type double in a table called Scenarios. I want to pass the field value to the function, convert from decimal to DMS (which is a string) and return the DMS string to extract the right 2 decimals from the seconds.

When I run the query, I get the following error message:
"Undefined function 'Dec_to_DMS' in expression"

Any help would be greatly appreciated.

The expression in the Expression Builder is:

Lat_DMS: Dec_to_DMS([Scenarios]![Latitude])

Here is the function I am using:

Public Function Dec_to_DMS(dblCoord As Variant)
  Dim strDMS As String
  Dim dblDeg As Double
  Dim dblMin As Double
  Dim dblSec As Double
  Dim arrSplit() As Variant

  strDec = Int(strCoord)
  
  'get decimal min
  arrSplit = Split(dblCoord, ".")
  dblMin = Int(arrSplit(1) * 60)
  
  'get decimal seconds
  arrSplit = Split(dblMinute, ".")
  dblSec = Round(arrSplit(1) * 60, 3)
  
  'get hemisphere
  If dblCoord < 0 Then  'neg so South or West
    strDMS = "-" & dblDeg & " " & dblMin & " " & dblSec
  Else    'pos so North or East
    strDMS = Str(dblDeg) & " " & Str(dblMin) & " " & Str(dblSec)
  End If
  
  dblCoord = strDMS
  
End Function

 

[RuralGuy]

I made two changes that are in RED.

Public Function Dec_to_DMS(dblCoord As Variant) [COLOR="Red"]As String[/COLOR]
   Dim strDMS As String
   Dim dblDeg As Double
   Dim dblMin As Double
   Dim dblSec As Double
   Dim arrSplit() As Variant

   strDec = Int(strCoord)

   'get decimal min
   arrSplit = Split(dblCoord, ".")
   dblMin = Int(arrSplit(1) * 60)

   'get decimal seconds
   arrSplit = Split(dblMinute, ".")
   dblSec = Round(arrSplit(1) * 60, 3)

   'get hemisphere
   If dblCoord < 0 Then  'neg so South or West
      strDMS = "-" & dblDeg & " " & dblMin & " " & dblSec
   Else    'pos so North or East
      strDMS = Str(dblDeg) & " " & Str(dblMin) & " " & Str(dblSec)
   End If

   [COLOR="Red"]Dec_to_DMS = strDMS
'   dblCoord = strDMS[/COLOR]

End Function

Your function was not returning anything for starters. Did you put this function in a standard module that is *NOT* named Dec_to_DMS?

 

[stringman]

I made the changes you suggested and now the function has no value w/in the parenthesis. I used to get "Dec_to_DMS(dblCoord)" after selecting the function. Now I get "Dec_to_DMS()". Anyway, the module is named modDec_to_DMS and the function is named Dec_to_DMS. I am still getting the same error. Thanks.

Ken

 

[RuralGuy]

How are you trying to use this function? What is the rest of the expression and where are you trying to use it?

 

[stringman]

I found the reason I was getting the "Undefined Function" error message. I had 2 copies of the same function inside the same module. Different editing stages. Pretty stupid.

I have also found other errors where the variable names were not consistent. The new function is below w/ changes in red:

Public Function Dec_to_DMS(dblCoord As Variant) As String
   Dim strDMS As String
   Dim dblDeg As Double
   Dim dblMin As Double
   Dim dblSec As Double
   Dim arrSplit() As Variant

   [COLOR="red"]dblDec[/COLOR] = Int([COLOR="Red"]dblCoord[/COLOR])

   'get decimal min
   arrSplit = Split(dblCoord, ".")
   dblMin = Int(arrSplit(1) * 60)

   'get decimal seconds
   arrSplit = Split([COLOR="red"]dblMin[/COLOR], ".")
   dblSec = Round(arrSplit(1) * 60, 3)

   'get hemisphere
   If dblCoord < 0 Then  'neg so South or West
      strDMS = "-" & str(dblDeg) & " " & str(dblMin) & " " & str(dblSec)
   Else    'pos so North or East
      strDMS = Str(dblDeg) & " " & Str(dblMin) & " " & Str(dblSec)
   End If

   Dec_to_DMS = strDMS
'   dblCoord = strDMS

End Function

Right now, using the function w/in the Expression Builder" is working, however I am getting a "Type Mismatch" error at the following line:

arrSplit = Split(dblCoord, ".")

I have tried converting dblCoord to a string and then parsing using the Split function, but that does not work either. I have a table containing lat and lon in decimal format and I want to create a query that converts each lat/lon record into DMS format. I can then interrogate each DMS value to get additional info I need.

 

[RuralGuy]

I have not attempted to analyze your function to see if it will work. I will leave that task to you. I'm just giving you some pointers to nudge you onto the right track. If you put:
Option Compare Database
Option Explicit
at the top of your module then most typos will be caught and it forces you to declare a variable before you use it. Why have you declared the incoming variable dblCoord as a Variant? Don't you know what type of field you have?

 

[stringman]

I used variant because the input is double and the output is a string. I think you fixed that problem by making the function and variable different types:

Function Dec_to_DMS(dblCoord as double) as String

 

[stringman]

RuralGuy,

Thanks for your help. Everything is working now. For some reason, setting arrSplit to variant instead of string was causing the "Type Mismatch" problem. Just some formatting issues for me to work out on the output.

FYI, here is final working function:

Public Function Dec_to_DMS(dblCoord As double) As String
   Dim strDMS As String
   Dim dblDeg As Double
   Dim dblMin As Double
   Dim dblSec As Double
   Dim arrSplit() As String
   Dim strTest As String

   dblDec = Int(dblCoord)

   'get decimal min
   strTest = Str(dblCoord)
   arrSplit = Split(strTest, ".")
   dblMin = Val("0." & arrSplit(1)) * 60)

   'get decimal seconds
   strTest = Str(dblMin)
   arrSplit = Split(strTest, ".")
   dblSec = Round(Val("0." & arrSplit(1)) * 60, 3)

   strDMS = Str(dblDeg) & " " & Int(dblMin) & " " & dblSec

   Dec_to_DMS = strDMS

End Function

Thanks again.

Ken

 

[RuralGuy]

Excellent! Thanks for posting back with your success.