Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 ensuring correct numeric formatting

Author  Topic 

osupratt
Posting Yak Master

238 Posts

Posted - 2014-12-03 : 17:24:54
I am adding two fields on some MS Access front-end forms that our clerks enter data into. Two of these fields are Latitude and Longitude. I need to ensure that when entering the Latitude that the number or numbers before the decimal point are positive. I also need to make sure there are at least 5 digits or places to the right of the decimal point. On the Longitude it needs to be a negative number with 5 decimal places. I now have these fields formatted to 'fixed' with 5 decimal places. I just don't know how to add some vb coding to fail if the clerks add something non-conforming. Any ideas or help pointing me in the right direction would help greatly. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-12-03 : 20:39:40
It's been a while but I used to do this in Access all the time. First thing to look at is the Input Mask property on your form controls. This will at least put some control on the input they type. You can then add a Validation Rule on the control to evaluate the input to make sure it meets your needs. Combining these two may be enough to manage latitude/longitude entries. I'd try that before digging into VB code.
Go to Top of Page

osupratt
Posting Yak Master

238 Posts

Posted - 2014-12-06 : 10:02:10
input mask I understand but could you possibly explain the validation rule you speak of?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-12-07 : 10:07:16
I created a basic form and added a text box control called "Lat". In the property sheet for Validation Rule I used the following:

[Lat] Like "[0-9].[0-9][0-9][0-9][0-9][0-9]" Or [Lat] Like "[0-9][0-9].[0-9][0-9][0-9][0-9][0-9]" Or [Lat] Like "[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]"

If I enter a value that doesn't meet those conditions it generates message and prevents me from exiting the control. I didn't work out the expression for longitude but you should be able to modify the above accordingly.

Edit: just noticed you indicated "at least 5 digits" to the right, this expression permits only 5 digits. I'll see if I can come up with a better expression.

Edit again: Here's some more info:

https://support.office.com/en-us/article/Restrict-data-input-by-using-a-validation-rule-6c0b2ce1-76fa-4be0-8ae9-038b52652320?ui=en-US&rs=en-US&ad=US
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-12-07 : 10:26:24
After thinking about this a bit more, this is a lot easier:

VAL([LAT]) BETWEEN 0 AND 90

I apologize for misreading this question and offering the original solution. Not enough coffee.

It's unnecessary to require 5 or more decimal places, since 65.5000000 is the same as 65.5. If you absolutely have to have at least 5 decimals:

VAL([LAT]) BETWEEN 0 AND 90 AND [LAT] LIKE "*.[0-9][0-9][0-9][0-9][0-9]*"
Go to Top of Page

osupratt
Posting Yak Master

238 Posts

Posted - 2014-12-24 : 15:41:54
Thanks so much for the help on this. I appreciate it greatly.
Go to Top of Page
   

- Advertisement -