Percent Data Type Issue

[JustinS]

I have a data entry form in which several of my fields use the percent data type. Works great on all but one. The problem that I have is that I need to allow entry of values less than 1%. The problem with this is that when for example my users enter say .98% access automatically converts .98 to 98%. That makes sense mathematically because .98 is equal to 98%. Further complicating this is that not all percentages will be less than 1% so a simple if statement like the one below doesn't work because it affects all entries:

Private Sub Claim_ AfterUpdate()


If Me.Claim < 1 Then
    Me.Claim = Me.Claim__.Value / 100
End If

end sub

Ideally, I would like the users to just be able to type in the percentage that they want without having to put in decimal format, ie 0.0098. This would reduce potential errors. A last resort would be to display a message box that notes the format that must be used for the field, but like I said that's not ideal.

Has anyone had experience with this problem, or know a work around to allow an input of less than 1% without having to enter it in decimal form?

 

[theDBguy]

Hi. Unfortunately, I can't think of a good workaround for this issue. Since .98 is going to be entered in decimal form anyway, it's just a matter of entering it as .0098, I guess. Sorry, if that's not helpful. Good luck!

 

[plog]

I would change it to plain numeric data input (with however many decimal points you want) and then put a label with the percent sign after it. If necessary to make it go into the table correctly you can put a hidden input on the form that converts the user input field into whatever value you want to go into the table.

 

[JustinS]

I was just trying avoid the need for the leading zeros. The problem that I fear may occur is that on other fields they will be able to enter 25 and the form will equate it to 25% which is actually .25. So if they become accustom to entering information that way it will cause errors on the one field that a specific format is required. I may just have to make a simple pop-up form that makes the user enter the information in the format that I prefer and transfers it back to the parent form. It's more work, but the data integrity is the most important thing in this case.

Thanks for the help.

 

[JustinS]

That's a good point @plog. Kinda have to trick the system to do what you want.

 

[GK in the UK]

I had this issue. I dealt with it by setting the data type to Currency, decimals Auto, on an unbound field.

If the user enters 12.75 I set the table value to 12.75/100 so it becomes .1275
Then I have a string function which sets the text field, it's (table value * 100) then add "%". So 12.75 is 'invisibly' saved as .1275 so all my calculations are right but it shows up as 12.75%, except at the moment of editing, when it shows up as 12.75

Works and displays the correct percentage from .01% to 99.99%

Sort of what Plog is saying

 

[arnelgp]

what type of form are you using? continuous, single, datasheet?