The percentage format of a field when creating a table

I'm trying to achieve the same thing on a form, but no satisfaction so far. Firstly, what goes on the front of =MakePercent([MyRate]), to avoid a compile error? If I give it the name of the field in the source table, it compiles. But when I pass a value through the function module, it comes out wrong.

This is my AfterUpdate procedure attached to the input text box on the form:

Private Sub ctlProjectPerCent_AfterUpdate()
ProjectPerCent = MakePerCent([ctlProjectPerCent])
Stop
End Sub

And this is the module procedu

Public Function MakePerCent(ctl As Control)
If InStr(ctl.Text, "%") = 0 Then
ctl = ctl / 100
End If
End Function

If I type 20 in the text box on the form, I expect to get 20% back. I get 20 going through to the MakePerCent function, converting to 0.02 at the end of the function. But it is passed back to the AfterUpdate procedure as 0, and it comes out as 2000.00% in the text box (which suggests that the value hasn't been passed back.

It's a while since I did any VBA in Access, so I've probably made a stupid mistake somewhere. Any ideas?

I am not sure of your set-up but there are a few problems with the posted
code:

1. You haven't declared the returned data type for your function
MakePerCent() which means that it will return a Variant by default. Use
strong typing whenever possible. This is not the problem, though.

2. You haven't assigned a returned *value* for the function either so the
function will *always* return Null regardless of what is in the Control.
Hence, I am not sure what you are trying to do with the function.

3. I think the statement "Stop" is not appropriate as used normally.

Check Access Help on the user-defined function and the Stop statement.

--
HTH
Van T. Dinh
MVP (Access)




"Peter Max" wrote in message
...
I'm trying to achieve the same thing on a form, but no satisfaction so
far. Firstly, what goes on the front of =MakePercent([MyRate]), to avoid a
compile error? If I give it the name of the field in the source table, it
compiles. But when I pass a value through the function module, it comes out
wrong.

This is my AfterUpdate procedure attached to the input text box on the
form:

Private Sub ctlProjectPerCent_AfterUpdate()
ProjectPerCent = MakePerCent([ctlProjectPerCent])
Stop
End Sub

And this is the module procedu

Public Function MakePerCent(ctl As Control)
If InStr(ctl.Text, "%") = 0 Then
ctl = ctl / 100
End If
End Function

If I type 20 in the text box on the form, I expect to get 20% back. I get
20 going through to the MakePerCent function, converting to 0.02 at the end
of the function. But it is passed back to the AfterUpdate procedure as 0,
and it comes out as 2000.00% in the text box (which suggests that the value
hasn't been passed back.

It's a while since I did any VBA in Access, so I've probably made a stupid
mistake somewhere. Any ideas?