Results 1 to 9 of 9
  1. #1
    edttom is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    3

    Question Query to add dot (.) in string? From 529645 -> 52.9645

    Hi all,



    I have a table containing coordinates, the problem is that the dot (.) is missing.
    The query should add a . after the 2nd character in the latitude column and after the 1st character in the longitude.

    Input:

    Latitude Longitude
    529645 604347

    Result:

    Latitude Longitude
    52.9645 6.04347


    Many thanks,

    Ed

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what code are you using in the query? and what datatype are your two fields?

  3. #3
    edttom is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    3
    Hi Ajax,

    I don't have a query yet, that is why I'm here. I was hoping you guys could fix me up with a nice query.
    Datatype is short text.

    Best,

    Ed

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    One way is to use the calculated fields in your query:

    NewLatitude: Left([Latitude],2) & "." & Right([Latitude],Len([Latitude])-2)
    NewLongitude: Left([Longitude],1) & "." & Right([Longitude],Len([Longitude])-1)

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is there always the same number of characters - i.e. 6? If so, you could use LEFT and RIGHT to create string values: Left(Latitude,2) & "." & Right(Latitude,4). If they are variable length strings then it becomes a bit more complicated.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is there always the same number of characters - i.e. 6? If so, you could use LEFT and RIGHT to create string values: Left(Latitude,2) & "." & Right(Latitude,4). If they are variable length strings then it becomes a bit more complicated.
    Based on the condition listed in the original question:
    The query should add a . after the 2nd character in the latitude column and after the 1st character in the longitude.
    the formulas I provided should always work, regardless of the length of the entries.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where did the data come from? If it includes a decimal point, then you might want to make the field single/double.

    But don't just change the data type, you'll lose info. Create a new field --double datatype, then create a query to update that field as you need it.
    Click image for larger version. 

Name:	RecordingLat_Long.jpg 
Views:	13 
Size:	48.8 KB 
ID:	25861

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    try

    NewLatitude: Left([Latitude],2) & "." & mid([Latitude],3)
    NewLongitude: Left([Longitude],1) & "." & mid([Longitude],2)

    or store as a double datatype - to convert

    NewLatitude: cdbl(Latitude & "/100")
    NewLongitude: cdbl([Longitude] & /10")

    which will make comparisons easier

  9. #9
    edttom is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    3
    Thanks all, the latlongs are variable in length, some are 5, most are 6.

    This did the trick, many thanks Ajax.

    Quote Originally Posted by Ajax View Post
    try

    NewLatitude: Left([Latitude],2) & "." & mid([Latitude],3)
    NewLongitude: Left([Longitude],1) & "." & mid([Longitude],2)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  2. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  3. Using a query instead of a SQL string with DAO
    By GraeagleBill in forum Programming
    Replies: 19
    Last Post: 10-08-2012, 03:11 PM
  4. String Query
    By Caysteve in forum Queries
    Replies: 7
    Last Post: 12-02-2011, 04:28 AM
  5. Replies: 0
    Last Post: 12-05-2005, 04:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums