third decimal place

[Rockape]

I'm trying to create a script that recognises that an input amount has gone beyond two decimal places.

I have multipled the amount by a thousand , then checked to see whether the last figre is greater than zero.

I have tried writing short script for it but to no avail. suggestions more than welcome

 

[chergh]

try something like this:

if cbool(instr(1,me.field.value,".")) = true then

   if len(Mid(me.field.value,instr(1,me.field.value,".")))>2 then
      msgbox("more than two  decimals")
   end if

end if

 

[Rockape]

try something like this:

if cbool(instr(1,me.field.value,".")) = true then

   if len(Mid(me.field.value,instr(1,me.field.value,".")))>2 then
      msgbox("more than two  decimals")
   end if

end if

Hi,

thanks for the reply.

I included this code in the event procedure for the On Enter property of the input box called [Expenditure].

Still no luck!

Private Sub Expenditure_Enter()
If CBool(InStr(1, Me.Expenditure.Value, ".")) = True Then

If Len(Mid(Me.Expenditure.Value, InStr(1, Me.Expenditure.Value, "."))) > 2 Then
MsgBox ("more than two decimals")
End If

End Sub

Can't figure out where i'm going wrong!!!!

 

[Rich]

Wrong event, use the BeforeUpdate event if you want to prevent the three places being entered

 

[gemma-the-husky]

rather than check the way you are doing, try

fld beforeupdateevent

dim chkvalue as double
chkvalue = cint(fld*100)/100
if chkvalue<>fld then
... errormessage
....cancel = vbcancel
end if

ie convert the entered value to an integer after multiplying by 100,
then divide by 100.
if you get a different answer, the original number had too many decimals

don't know if anyone has a more efficient way of doing this

 

[Rockape]

hi all,

thanks for your replies. No luck still!

Tried previous code on the beforeupdate, but I think it was missing an extra end if at the end.

Nevertheless it doesn't work.

Tried new code, it wont budge.
Does it matter if I already have a validation rule included and a validation text too?

Anyway tried this but nothing.

Private Sub Expenditure_BeforeUpdate(Cancel As Integer)

Dim chkvalue As Double
chkvalue = CInt(fld * 100) / 100
If chkvalue <> fld Then
MsgBox ("more than two decimals")
End If

End Sub

Regards all

rather than check the way you are doing, try

fld beforeupdateevent

dim chkvalue as double
chkvalue = cint(fld*100)/100
if chkvalue<>fld then
... errormessage
....cancel = vbcancel
end if

ie convert the entered value to an integer after multiplying by 100,
then divide by 100.
if you get a different answer, the original number had too many decimals

don't know if anyone has a more efficient way of doing this

 

[boblarson]

Okay - here you go. This should work for you:

If Len(Mid(CStr([YourFieldNameHere]), InStr(1, [YourFieldNameHere], ".", vbTextCompare) + 1)) > 2 Then
    MsgBox "Too many decimals"
    Cancel = True
End If

 

[Rockape]

Thanks it worked..

regards

Okay - here you go. This should work for you:

If Len(Mid(CStr([YourFieldNameHere]), InStr(1, [YourFieldNameHere], ".", vbTextCompare) + 1)) > 2 Then
    MsgBox "Too many decimals"
    Cancel = True
End If

 

[Rockape]

Hi,

Any chance of having it explained.... Thanks once again!

Thanks it worked..

regards

 

[boblarson]

Hi,

Any chance of having it explained.... Thanks once again!

1. I have it converting to a string

2. I then am using the mid function to find the decimal location and

3. check the length of the string after the decimal point

4. If the length of the decimal point is greater than 2 it has more than 2 decimal places.

Hope that helps

 

[Rockape]

Thanks once again,

May I be so bold as to try your patience again?

I understand the conversion of Expenditure to a string, but could you explain the remaining expression,
for example does
InStr(1, [Expenditure], ".", vbText Compare) return the value 1?

what about Len and Mid

Kind regards

1. I have it converting to a string

2. I then am using the mid function to find the decimal location and

3. check the length of the string after the decimal point

4. If the length of the decimal point is greater than 2 it has more than 2 decimal places.

Hope that helps

 

[chergh]

The help file in access will explain the instr, len and mid functions

 

[Brianwarnock]

The help file in access will explain the instr, len and mid functions

But if Rock is a newbie he may not have realised that he has to get into the VBA editor help and type mid function in the answer wizard, you wont find it in the index, Access help is not really intuitive or helpful.

Brian

 

[chergh]

But if Rock is a newbie he may not have realised that he has to get into the VBA editor help and type mid function in the answer wizard, you wont find it in the index, Access help is not really intuitive or helpful.

Brian

You don't need to be in the VBE to get to the help files on functions. Just type "mid function" into the search box, after you hit the help icon, and the second result is an alaphabetical list of functions. If your creating something on Access you should be able to use the help function, though now I think about some of the posts we see on these forums I may be very wrong about that.

 

[Rockape]

hi,

Thanks all for your kind attention. Will lookup the help in access.

Regards

You don't need to be in the VBE to get to the help files on functions. Just type "mid function" into the search box, after you hit the help icon, and the second result is an alaphabetical list of functions. If your creating something on Access you should be able to use the help function, though now I think about some of the posts we see on these forums I may be very wrong about that.

 

[boblarson]

Thanks once again,

May I be so bold as to try your patience again?

I understand the conversion of Expenditure to a string, but could you explain the remaining expression,
for example does
InStr(1, [Expenditure], ".", vbText Compare) return the value 1?

what about Len and Mid

Kind regards

Instr is looking for a particular string within another string. In this case we are looking for the period and the function returns the character position number of the starting point of the string you are looking for.

Len returns the length of the string

Mid lets you return something from inside a string.

Now with those quick explanations in place, I would also echo the "look in the help files" to get more info. They have all of the syntax info that you need. And just to add to what's been said about the location of the help file information - some function information is in the regular help file and some additional information is in the VBA help file which is available when you are in the VBA IDE (Integrated Development Environment) Window. You can get there by either opening a module in design mode or by opening a form in design mode and clicking VIEW > CODE. Then you can hit F1 to bring up the help file.