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.
| 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. |
 |
|
|
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? |
|
|
|
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 |
|
|
|
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 90I 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]*" |
|
|
|
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. |
|
|
|
|
|
|
ensuring correct numeric formatting