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

I made two changes that are 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

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

Dec_to_DMS = strDMS
' dblCoord = strDMS

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

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

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

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

dblDec = Int(dblCoord)

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

'get decimal seconds
arrSplit = Split(dblMin, ".")
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.

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?

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

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

Excellent! Thanks for posting back with your success.