Is it possible to narrow an input mask to 4 letter of the alphabete? (1 Viewer)

[Mat1994]

Hi All,

In my form, I have a two fields for the latitude and longitude.
The data is a combination of numbers and one lettre (N, S, E or W). The input mask is : 999\°99\'99"''"L

I've used an input mask "L" for the letter. However I would like to know if it was possible to limit the letter to (N, S, E or W)?

In addition :
The first 999 is limit to a number between 0 and 359
The second and third 99 are limited between 0 and 59

For the numbers, is it possible to limit the group of number to an interval?

Thank you for your help,
Mat

 

[theDBguy]

Hi Mat,

Probably not by using an input mask but maybe by using a validation rule. However, if your requirements are not simple, then perhaps it’s better to do data validation at the form level, where users should be inputting the data anyway.

 

[MajP]

I would have three unbound controls on a form. Text box that validates 0-359, text box that validates to 0-59, and a combobox that validates N,S,E,W. Then on the after update of each you check if all three are filled and update your field concatenating as necessary. Then you show it in a bound control that is locked for editing.

 

[Mat1994]

Thanks,
Is it possible to combine both, the input mask and a validation rule?

I've never used a validation rule, so I'll have to check it out.

Mat

 

[Mat1994]

Hi, MajP
I'll see if I can limit myself with a validation rule so I just have one field to deal with. Otherwise, your method is probably the easiest.

Mat

 

[MajP]

I'll see if I can limit myself with a validation rule so I just have one field to deal with

I do not think so, seems to complex. But on a form in the before update of your control you could build a vba function to validate if the last character is E, N, S, W and then cancel the before update forcing them to update. So I think you could use your current input mask with an additional validation in vba.

Another reason you may want to consider something like I was suggesting is that it will be a lot easier doing something (like calculating distance) with a numeric lat long instead instead of a string. I would store these values and not store that string representation. I would display it on a form or a query as needed. From those three values you can get the degrees decimal minutes which you can do the math with. If you store it as a string you will always have to parse it to get the values.

 

[Mat1994]

Ok, your method does seem better.
I'll try it out.

Thanks,
Mat

 

[MajP]

For example if you save these as signed lat longs (W, S are negative) then in vba you can get the distance.

Private Function GetPolarDistance(decLatStart As Single, decLongStart As Single, decLatEnd As Single, decLongEnd As Single) As Single
    Const decToRad = 3.14159265358979 / 180
    Const radiusOfEarth = 3963.1
    'radiusOfEarth =3963.1 statute miles, 3443.9 nautical miles, or 6378 km
    Dim radLatStart As Single
    Dim radLongStart As Single
    Dim radLatEnd As Single
    Dim radLongEnd As Single
    radLatStart = decLatStart * decToRad
    radLongStart = decLongStart * decToRad
    radLatEnd = decLatEnd * decToRad
    radLongEnd = decLongEnd * decToRad
    GetPolarDistance = ArcCos((Cos([radLatStart]) * Cos([radLongStart]) * Cos([radLatEnd]) * Cos([radLongEnd])) + Cos([radLatStart]) * Sin([radLongStart]) * Cos([radLatEnd]) * Sin([radLongEnd]) + (Sin([radLatStart]) * Sin([radLatEnd]))) * radiusOfEarth
    '                     (cos($a1)*            cos($b1)*             cos($a2)*          cos($b2)          + cos($a1)*            sin($b1)*              cos($a2)*          sin($b2) +          sin($a1)*             sin($a2)        ) * $r
    '                 acos((cos($a) *           cos($b) *             cos($c) *          cos($d)) +          (cos($a) *           sin($b) *              cos($c) *           sin($d)) +         (sin($a) *            sin($c)) ) * $r
End Function

Private Function ArcCos(X As Single) As Single
    If Abs(X) <> 1 Then
        ArcCos = 1.5707963267949 - Atn(X / Sqr(1 - X * X))
    Else
        ArcCos = 3.14159265358979 * Sgn(X)
    End If
    'ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

This post has a lot of other code for using lat, longs such as bearings, midpoints, final destination.
https://www.movable-type.co.uk/scripts/latlong.html

 

[Mat1994]

Thank, your post is interresting but I don't need to convert latitude/longitude in distances.

I created 4 unbound controls in my form.
- LAT_degrees for the latitude degrees. Input mask is 99
- LAT_Minutes for the latitude minutes. Input mask is 99
- LAT_Secondes for the latitude secondes. Input mask is 99
- Lat_Cardinal for the cardinal directions. Combo box with the possible values of "N" or "S"

I have also a bound field called "Latitude" where I would like this fields to populate itself after the 4 unbound fields are inputted. This is to allow me to store the latitude is one field in a table.

My problem : I've tried to autopopulate the "Latitude" with the 4 unbound fields. With no success.

I tried doing a "before update" in the field "Latitude" with the code :
Me.Latitude = str(LAT_degrees) + str(LAT_Minutes) + str(LAT_Secondes) + str(Lat_Cardinal)

It didn't work. So I tried, still in the "before update".
Me.Latitude = str(LAT_degrees) & str(LAT_Minutes) & str(LAT_Secondes) & str(Lat_Cardinal)

It doesn't work.

In addition of having the latitude autopopulate itself. I would like the field to be displayed like : 99\°99\'99"'"L.

Could you help me autopopulate the "latitude" with the 4 unbound field and make it comply with the input mask 99\°99\'99"'"L?

Thank you for your help,
Mat

 

[MajP]

Public Function GetStringLatLong(degrees As Long, minutes As Long, seconds As Long, Direction As String) As String
  GetStringLatLong = degrees & Chr(248) & " " & minutes & "' " & seconds & "'' " & Direction
End Function

me.somefield = getStringLatLong(me.LAT_degrees,me.LAT_Minutes,me.Lat_Seconds,me.Lat_Cardinal)

IMO I would not save the string but use this function on a form or query to show the string.

I tested this in the immediate window passing in literals

?getStringLatLong(123,45,23,"N")
123ø 45' 23'' N

 

[MajP]

On the form I would likely wrap this function and you call on the after update of each of those textboxes

Public Function CompleteLatLong() as booean
  if not (isnull(me.Lat_Degrees) or isnull(me.lat_Minutes) or isnull(me.lat_Seconds) or isnull(me.Lat_Cardinal)) then 
   me.somefield = me.somefield = getStringLatLong(me.LAT_degrees,me.LAT_Minutes,me.Lat_Seconds,me.Lat_Cardinal)
  end if 
end if

You need to make sure all values are filled in before calling the function

 

[Mat1994]

Thanks for your help.
I don't fully understand you.

-Where do you write a public function? I've only worked with events, so I'm only know private sub.
-Why would you not save the string and use the function?

Mat

 

[MajP]

Why would you not save the string and use the function?

If you save a calculated value, you have to make sure everytime you change an input that you run code to update. If when you want to show the string you calculate it on the fly it will always be in synch. So in a database I put in Price and Quantity, but never put in TotalCost. If I want to show total cost I do this in a query like
Select Price, Quantity, (Price * Quantity) As TotalCost from sometable
or in a field on a form
=[Price]*[Quantity]
If you really only need the string and not going to actually do sorts or searches or comparisons on a real value then you may be OK by just saving the string. I personally would save either decimal degrees (you can use the unbound to populate this) and the cardinal or the individual deg, min, sec and the cardinal. This is similar to working with dates. If you save a date or time as a string, you end up creating a lot of difficulty. To get something as simple as elapsed time takes all kinds of conversions.

 

[Mat1994]

I would prefere showing the full latitude value in the form and not using a query.
I can create a button that calculates the latitude after the deg, min, sec and cardinal are inputted.
However, I'm not sure to understand how to add the deg, min, sec and cardinal values and seperating them with ° and '.

 

[MajP]

So my recommendation, just in case you are going to do something with this value, is to store the decimal degrees and the cardinal. You can use the unbound fields to help. This would be a change to what I said before.

I would put the functions in a standard module then you can call them from anywhere. A form, report, query or other code. If you drop it in the form module you will only be able to use it there.

Public Function GetDecimalDegrees(degrees As Long, minutes As Long, seconds As Long) As Double
   GetDecimalDegrees = degrees + minutes / 60 + seconds / 3600
End Function

Public Function GetStrLatLongFromDec(decimalDegrees As Double, Cardinal As String) As String
  Dim deg As Long
  Dim min As Long
  Dim sec As Long
  deg = Int(decimalDegrees)
  min = Int((decimalDegrees - deg) * 60)
  sec = (decimalDegrees - deg - min / 60) * 3600
  GetStrLatLongFromDec = deg & Chr(248) & " " & min & "' " & sec & "'' " & Cardinal
End Function

This function you just drop in the form module

Public Sub FillDecLatLong() 
  if not (isnull(me.Lat_Degrees) or isnull(me.lat_Minutes) or isnull(me.lat_Seconds) ) then 
   me.DecimalDegrees = getDecimalDegrees(me.LAT_degrees,me.LAT_Minutes,me.Lat_Seconds,)
  end if 
end if

So if you have a field added called DecimalDegrees then in the after update of the unbound textboxes you simply call the code
FillDecLatLong. If the boxes for deg, min, sec are filled in then the code fires.

Now if you want to show the string you can do that in an unbound textbox or add it to the query
=GetStrLatLongFromDec([DecimalDegree],[Cardinal])

I will upload a demo.

 

[Mat1994]

I don't need any calculation done, so I don't think I need your first code. Or I didn't understand them.

If I show the string in an unbound field, I won't be able to store the value in a table?

My thought were :
I have 4 unbound fields (deg, min, sec, cardinal). I could create a button with a on-click event that populate a bound field called Latitude.

What do you think?

 

[MajP]

This is a pretty week demo. But it shows some concepts. If I was doing this and not save deg, min, sec but only decimaldegrees then in the unbound I would populate the deg, min, sec if a value already exists.

 

[MajP]

Here is a hardwired version in Demo2. It saves the value into a field strLatLong. You can leave are remove the decimalDegrees. But it is a lot easier to do conversion from there.

 

[June7]

I have worked with coordinate data quite a bit and always store as decimal degrees (as a + or - value) then formatting to the dd:mm:ss string with a calculation. The Cardinal does not have to be saved but doesn't hurt.

 

[Mat1994]

thanks a lot for the demo. It's what I'm trying to do.

I don't understand : GetStringLatLong
What does the LatLong refere to? Because I copied/pasted your code, but I get an error : "Compile error : Sub or Function not defined". I replaced your GetStringLatLong with GetStringLatitude, which is the field I'm trying to autopopulate.

June7, for the moment, I working with degrees, minutes, seconds because the software we are going to use read the latitude that way.

Mat