Correcting a Field From Another Application 2

[kshadden]

I have a field that comes from another application that when imported into Access looks like 95A45'4908"W. I want to run a query that will convert the field to 95.454908. How can I do that?

 

[lespaul]

Is the original field ALWAYS structured like that?

Then you can use the MID, LEFT and/or RIGHT functions to extract specific pieces that you need:

SELECT LEFT(FieldName, 2) + "." + MID(FieldName, 4, 2) + MID(FieldName, 7, 4) From TableName






Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[gmmastros]

That value looks like a longitude value stored in degrees/minutes/seconds which is NOT the same as 95.454908.

The W on the end means travel West. West values for longitude are represented by a negative number when converted to numeric representation.

To convert your value:

-1 * (95 + (44/60) + (49.08/3600))

So, the longitude value in your example should be converted to -95.74697





-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kshadden]

Thank you both for your help. I have been able to make it work!