Re: [MS_AccessPros] Convert latitude/longitude DMS to DD

 

Paul-

Assuming your conversion formula for Excel works (not sure why it uses only every other character), the ASC function may do the trick.  In a query design grid:

Lat:  (ASC(MID(A2,2,1))-65)*10 + VAL(MID(A2,4,1)) + (ASC(MID(A2,6,1))-97)/24 + 1/48 - 90

Let me know if that works for you.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Jan 12, 2017, at 23:53, kiener@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Greetings and thanks for the welcome to the group!

I've tried searching conversations in this group, but have not found any previous discussions.

I have a MS Access database linked to a Ham Radio logging program.  The latitude and longitude in the logging program has the latitude and longitude fields listed in Degree Minute Second format.  I would like to convert that to Decimal Degrees.  (I need the DD format in order to export data for use in Google Maps Api, which uses the DD format).

I've tried researching for an easy formula, but I find scripts for the conversion in different programming languages.  My knowledge of even Visual Basic is nill, so those scripts are beyond me.  I'm hope that there may be a simple formula that I could use.

If this is process is too complicated for me, another option is to convert gridsquares into Decimal Degrees.  (Gridsquares denote a certain location in the form, for example, EM69tt, denotes a location in Indianapolis, IN).  Gridsquares are also a field in the logging program to which I can link.

I have found formulas that work in Excel, but the same functions do not exist in Access (unless by another name).  Those formulas:

for Lat:  =(CODE(MID(A2,2,1))-65)*10 + VALUE(MID(A2,4,1)) + (CODE(MID(A2,6,1))-97)/24 + 1/48 - 90;

for Lon:  =(CODE(MID(A2,1,1))-65)*20 + VALUE(MID(A2,3,1))*2 + (CODE(MID(A2,5,1))-97)/12 + 1/24 - 180

("A2" obviously refers to the reference cell which contains the Gridsquare).

I hope that my question is clear and also hope that someone may be able to help me.  Thank you in advance for any help or suggestion.

Paul Kiener

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___