Results 1 to 11 of 11
  1. #1
    Join Date
    May 2017
    Posts
    17

    MS Access vba AfterUpdate coding question.

    Hi all, this is my first post here (I tried posting this question in PCReview, but no replies so far). I only know a little about coding, thus this question please.



    In my MS Access 2003 dbase, there are three text fields in one of the tables:

    One field is called 'LATITUDE' (sample data in this field is -29 27 36.00) (please note the minus sign in this example, as this field has mostly southern hemisphere latitudes, but can sometimes be northern hemisphere latitudes in which case there would be no minus sign. Presumably a latitude exactly on the equator would be 00 00 00.00. There is never a need for a latitude at either of the poles)

    Another field is called 'LONGITUDE' (sample data in this field is 115 00 57.90).

    The third field is called 'LATLONGWITHDEGMINSEC' (which is blank, but if filled, using the above examples, would show
    29°27'36.00"S 115° 0'57.90"E).

    At present I am completing that third field manually, which is time-consuming. I would please like a bit of vba code to put in the AfterUpdate property for each of the LATITUDE and LONGITUDE fields so that as each of those two fields are updated, the LATLONGWITHDEGMINSEC field is automatically filled to eventually show the concatenation 29°27'36.00"S 115° 0'57.90"E.

    It would also be handy to have code to do the reverse conversion at some stage, as the format of where I source lats and longs can vary, e.g. sometimes I only get the lat and long in the format 29°27'36.00"S 115° 0'57.90"E, and thus need to enter -29 27 36.00 in the LATITUDE field, and similarly enter 115 00 57.90 in the LONGITUDE field, currently also done manually.

    Any and all help would be very much appreciated, many kind thanks indeed.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Solution depends on the data consistency. It's one thing if lat is always ##space##spaceremainder (i.e. only one space) and the same can be said for long, then maybe not too difficult. Same goes for the second request, although I see you have a space in long but not lat. If you could post a data dump that shows as many variations as possible, that would help. Suggest you dump into Excel and weed out most of the similar stuff so that variations are shown. Center all the entries, and copy/paste in a post. You should get a pretty decent html table. If need be, you can "Go Advanced" here and increase the table width easily enough.

    BTW: we/I appreciate you declaring this has been posted elsewhere, but etiquette suggests you post a link to any other post for this issue in each forum so we can keep tabs. Much appreciated by all if you update (mark as solved) in both places if that happens. Something like this can take hours, and speaking for myself, the last thing I want to do is spend a lot of time on something that has been or is close to being solved elsewhere.
    Last edited by Micron; 05-23-2017 at 08:30 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You will need to do as micron suggests and provide a full set of examples - for example from your latitude value -29 27 36.00 we can infer that because it starts with a minus sign, you use S in the desired result - but what if there if there is no minus sign? a space? nothing at all? something else such as a +? And if no - sign, what to use instead of S? (I assume N) but what if you are on the equator so neither north nor south?

    Yes we could go away and read up on the rules on presenting longitude and latitude values, but we also need to know how consistent your data is so a set of 'conversion rules' can be created for conversion.

    just for my curiosity - why there a space between 115° and 0'57.90"E, but not between 29° and 27'36.00"S in your example (repeated several times)

    29°27'36.00"S 115° 0'57.90"E

  4. #4
    Join Date
    May 2017
    Posts
    17

    MS Access vba AfterUpdate coding question

    Many kind thanks Micron and Ajax. I will see what i can do to assemble the latlong variations in the next day or so, and provide further clarification. Yes, my apologies, you are right, my example 29°27'36.00"S 115° 0'57.90"E varies as to spaces. I got the lat and long for that directly from the properties field for a placemark for that location when pasted into the search box in Google earth. I use Google earth (Ge) to visualise and verify (or correct if necessary) where my lats and longs are located, as recorded by me on a 'site bag label' for insect specimens I collect or observe when I am in the field here in Australia surveying for certain insects (unpaid stuff I do as I have a lifelong passion as an amateur entomologist). On the site bag I simply write the lat long in the simplest 'plain' format, thus the source of the made-up example I gave of -29 27 36.00 115 00 57.90, which is of course a Southern Hemisphere latitude. In response, Ge zooms to the latlong on Australia on the Ge globe and creates a Placemark for that latlong. I then go to the properties for that Placemark and copy into my dbase the same lat and long, which Ge formats separately as 29°27'36.00"S and as 115° 0'57.90"E.

    The presence or absence of spaces in these lats and longs is something I can't control as Ge seems to vary with or without spaces as it sees fit, though usually without. Try it yourself, enter
    -32 06 09.30 149 52 30.80 into Ge and you will see that the lat given in the properties for the placemark it creates has a space, but if you enter -35 46 07.10 141 23 10.95 into GE then the properties shows no space in the latitude. Beats me, so I copy 'as is' and it does not seem to worry anyone either way when I send in my reports, though I am sure its an important coding consideration.

    Ajax asks ' what if there if there is no minus sign? a space? nothing at all?' The answer is 'nothing at all'. For example, for a specimen I have from say the Philippines, the lat long would be, being in the Northern Hemisphere, put in the LATITUDE field as 14 29 08.66 9 (thus if converted is 14°29'8.66"N in part of the LATLONGWITHDEGMINSEC field ) and in the LONGITUDE field as 121 19 52.97 (converts to 121°19'52.97"E), or vice versa.

    Anyway, I simply copy GE placemark lats and longs and put them together in the LATLONGWITHDEGMINSEC field with a space in between to show as
    29°27'36.00"S 115° 0'57.90"E. I need these and other various latlong formats as I collect insects under different scientific licences in different Australian States, and each has different latlong format reporting requirements.

    Also, a friend kindly gave me code and some xml and .js files (whatever they are), which I don't understand most of, to allow me to trigger from a command button on a form in my dbase the sending of latlongs to Ge and displays them all on the Ge globe. Thus for example I can throw at Ge all my records for a certain insect species to see its distribution within Australia. Anyway, the code he gave me uses the separate LATITUDE (i.e.
    -29 27 36.00) and LONGITUDE (i.e. 115 00 57.90) fields, but i guess he could have used the LATLONGWITHDEGMINSEC field instead.

    Since most of my source latlongs are from the latlongs I wrote on my specimen site bags when I was out bush (e,g, -29 27 36.00 115 00 57.90), and, when back home , as I usually first type that into GE and create a placemark in Ge and grab its 'with degrees, minutes and seconds symbols' format separate lat and long, its probably best that I only need AfterUpdate code to convert 29°27'36.00"S 115° 0'57.90"E so as to write -29 27 36.0 in the LATITUDE field and write 115 00 57.90 in the LONGTITUDE field. No conversion from degs mins second to decimal degrees or eastings and northings is presently required, nor worrying about anything on the equator. I am told that what I need is a matter of 'string manipuation', thus adding or removing the symbols (depending on which way I need to convert, in most cases from the 'symbol format' (i.e. with deg min sec symbols and S and E) to the plain form (i.e. -29 27 36.00 115 00 57.90), and removing (or adding) the minus sign (and not replacing/replacing with a space depending on what is being converted to what) e.g. adding S at the end of the latitude part of 29°27'36.00"S 115° 0'57.90"E, and adding E at the end of the longitude part of 29°27'36.00"S 115° 0'57.90"E.

    Thanks for the etiguette tip, will do. Not sure how to put a link here but the URL (no response as yet) is https://www.pcreview.co.uk/threads/m...e-fix.4072205/

    Cheers, Allen

    Quote Originally Posted by Micron View Post
    Solution depends on the data consistency. It's one thing if lat is always ##space##spaceremainder (i.e. only one space) and the same can be said for long, then maybe not too difficult. Same goes for the second request, although I see you have a space in long but not lat. If you could post a data dump that shows as many variations as possible, that would help. Suggest you dump into Excel and weed out most of the similar stuff so that variations are shown. Center all the entries, and copy/paste in a post. You should get a pretty decent html table. If need be, you can "Go Advanced" here and increase the table width easily enough.

    BTW: we/I appreciate you declaring this has been posted elsewhere, but etiquette suggests you post a link to any other post for this issue in each forum so we can keep tabs. Much appreciated by all if you update (mark as solved) in both places if that happens. Something like this can take hours, and speaking for myself, the last thing I want to do is spend a lot of time on something that has been or is close to being solved elsewhere.

  5. #5
    Join Date
    May 2017
    Posts
    17
    Hi Micron & Ajax, I just spent the last 2 hours writing a detailed reply, hit 'Submit Reply', but it vanished! Can anyone see it? I have no idea what happened to it. Allen.

    Quote Originally Posted by Allen Sundholm View Post
    Hi all, this is my first post here (I tried posting this question in PCReview, but no replies so far). I only know a little about coding, thus this question please.

    In my MS Access 2003 dbase, there are three text fields in one of the tables:

    One field is called 'LATITUDE' (sample data in this field is -29 27 36.00) (please note the minus sign in this example, as this field has mostly southern hemisphere latitudes, but can sometimes be northern hemisphere latitudes in which case there would be no minus sign. Presumably a latitude exactly on the equator would be 00 00 00.00. There is never a need for a latitude at either of the poles)

    Another field is called 'LONGITUDE' (sample data in this field is 115 00 57.90).

    The third field is called 'LATLONGWITHDEGMINSEC' (which is blank, but if filled, using the above examples, would show
    29°27'36.00"S 115° 0'57.90"E).

    At present I am completing that third field manually, which is time-consuming. I would please like a bit of vba code to put in the AfterUpdate property for each of the LATITUDE and LONGITUDE fields so that as each of those two fields are updated, the LATLONGWITHDEGMINSEC field is automatically filled to eventually show the concatenation 29°27'36.00"S 115° 0'57.90"E.

    It would also be handy to have code to do the reverse conversion at some stage, as the format of where I source lats and longs can vary, e.g. sometimes I only get the lat and long in the format 29°27'36.00"S 115° 0'57.90"E, and thus need to enter -29 27 36.00 in the LATITUDE field, and similarly enter 115 00 57.90 in the LONGITUDE field, currently also done manually.

    Any and all help would be very much appreciated, many kind thanks indeed.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sorry - no!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Allen Sundholm View Post
    Hi Micron & Ajax, I just spent the last 2 hours writing a detailed reply, hit 'Submit Reply', but it vanished! Can anyone see it? I have no idea what happened to it. Allen.
    It was moderated, now approved.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK so for east/west - is a W used? or left blank? and at what point would it change? 180?

    Suggest rather than a lot of text, try and write it out as a formula e.g.

    for latitude: if first character ='-' then S, otherwise N
    for longitude:=if first characters are numeric and less than or equal to 180 then E, otherwise W

    etc

    at the moment I think you need something like for latitude

    replace(replace(replace(latititude,"-","")," ",chr(176),,1)," ","'") & chr(34) & iif(val(latititude)<0,"S","N")

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Ajax; I LOVE that solution.

    @Allen: I tried your test and am pretty sure I did not get the same result. They both appeared to have the same format as far as I could tell.
    Doesn't matter if I did it wrong I guess; we'll just wait for more data from you (at least I will).

    Click image for larger version. 

Name:	latLong1.jpg 
Views:	15 
Size:	2.9 KB 
ID:	28860 Click image for larger version. 

Name:	latLong2.jpg 
Views:	16 
Size:	9.5 KB 
ID:	28861
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    May 2017
    Posts
    17

    MS Access vba AfterUpdate coding question

    Hi Micron,

    Yes, the image you posted is for the Placemark, but that's only the label. The lats and longs with degrees, minutes and seconds symbols, and spaces (or not) are shown in the Properties for each Placemark (right-click the Placemark on the Ge globe of Earth and select Properties), as per the images I attach below. They are of the top parts of the Properties box that appears after right-clicking the Properties for a Ge Placemark. As you can see, Ge has a space in one of the latitudes, but not the other. I now believe I know what Ge is doing: if the minutes of a latitude (and/or presumably a longitude) is a single digit, e.g. 6 minutes instead of say 25 minutes, Ge drops the 0 (zero) from 06 and inserts a space in front of the 6 instead.

    Cheers!
    Allen

    Click image for larger version. 

Name:	GE Properties 2.jpg 
Views:	12 
Size:	38.4 KB 
ID:	28863Click image for larger version. 

Name:	GE Properties 1.jpg 
Views:	12 
Size:	38.5 KB 
ID:	28864

  11. #11
    Join Date
    May 2017
    Posts
    17

    MS Access vba AfterUpdate coding question

    Hi Micron and Ajax,

    Here is a screen shot of one of the (many) forms in my dbase which shows a subform with the records in Datasheet format (easy for me to complete this way especially if I have multiple records of the same species from the same site, in which case I just copy and paste, thus adding a new record for each specimen). The screen shot shows just some of the fields I use for each record, but we are now only concerned with the fields LATITUDE, LONGITUDE and LATLONGWITHDEGMINSEC. So, after I copy the lats and longs from the Properties box of a Ge placemark (which Ge creates when I enter the 'plain' form of lat and long I wrote on the specimen site bag when I was out bush) and paste then, with a space, the lats and longs Ge shows in 'symbol' format into the LATLONGWITHDEGMINSEC field, it would be a wonderful time-saver if, after my updating that LATLONGWITHDEGMINSEC field (thus using the AfterUpdate event for that field) the LATITUDE and LONGITUDE fields were automatically filled in, in the 'plain' format as seen in the fields LATITUDE and LONGITUDE in the screen shot. Currently I am having to fill those fields in manually. I am told it is a matter of 'string manipulation'. If I can get a handle on such string manipulation I can probably work out myself how to do several others that I'd like to build in.

    Many thanks for all your kind help, Allen


    Click image for larger version. 

Name:	Dbase screen shot 1.jpg 
Views:	12 
Size:	208.3 KB 
ID:	28865

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

Similar Threads

  1. Need Help with Access vb coding
    By Entrimo in forum Access
    Replies: 1
    Last Post: 02-19-2012, 06:15 PM
  2. Access without coding
    By kp123 in forum Access
    Replies: 4
    Last Post: 11-25-2011, 03:50 PM
  3. Quick coding-orginization question
    By Ashe in forum Forms
    Replies: 2
    Last Post: 09-16-2011, 09:35 AM
  4. Coding question
    By kzoli62 in forum Access
    Replies: 1
    Last Post: 07-04-2011, 05:09 PM
  5. BeforeUpdate and AfterUpdate Question
    By gsurfdude in forum Programming
    Replies: 0
    Last Post: 04-03-2008, 07:56 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