Pennies only

C

CJ

Hi Groupies

Is it possible to format a currency field so that no dollars show up? I
would like my user to be able to enter in the value 15 and have it input as
$0.15, or if they enter 8 it would show up as $0.08.

Can this be done?
 
J

John W. Vinson

Hi Groupies

Is it possible to format a currency field so that no dollars show up? I
would like my user to be able to enter in the value 15 and have it input as
$0.15, or if they enter 8 it would show up as $0.08.

Can this be done?
Click to expand...

You can put code in the AfterUpdate event of the textbox:

Private Sub txtMyTextbox_AfterUpdate
If Me.txtMyTextbox > 1.0 Then
Me.txtMyTextbox = Round(Me.txtMyTextbox / 100, 2)
End If
End Sub

This checks to see if the value is already under a dollar; if not, it divides
by 100 and rounds the result to the nearest penny.

John W. Vinson [MVP]
 
C

CJ

John W. Vinson said:
You can put code in the AfterUpdate event of the textbox:

Private Sub txtMyTextbox_AfterUpdate
If Me.txtMyTextbox > 1.0 Then
Me.txtMyTextbox = Round(Me.txtMyTextbox / 100, 2)
End If
End Sub

This checks to see if the value is already under a dollar; if not, it
divides
by 100 and rounds the result to the nearest penny.

John W. Vinson [MVP]
Click to expand...


Brilliant, thanks John!

That is exactly what I needed.
 
J

John W. Vinson

Brilliant, thanks John!

That is exactly what I needed.
Click to expand...

The disadvantage is that if a user types 112 (expecting to get $1.12) they'll
get $112. You can leave off the IF... END IF lines if you trust your users
NEVER to hit the decimal key; the downside is that if a user type $0.83
they'll get $0.0083 which will round to one cent.

John W. Vinson [MVP]
 
C

CJ

John W. Vinson said:
The disadvantage is that if a user types 112 (expecting to get $1.12)
they'll
get $112. You can leave off the IF... END IF lines if you trust your users
NEVER to hit the decimal key; the downside is that if a user type $0.83
they'll get $0.0083 which will round to one cent.

John W. Vinson [MVP]
Click to expand...


Thanks for the heads up John. At some point the people entering in the data
have to take some responsibility for their actions. The calculated results
in this case should make any errors very evident.

Thanks again.
CJ
 
B

BruceM

CJ said:
Thanks for the heads up John. At some point the people entering in the
data have to take some responsibility for their actions. The calculated
results in this case should make any errors very evident.

Thanks again.
CJ
Click to expand...
The operative word here is "should". If the amount is expected to be within
a certain range you could have a message box appear if it falls outside of
that range. In a database used to log training the time is entered in hours
or decimal fractions (to the nearest quarter hour), so that 30 minutes is
..5. I have seen it entered as 30 hours in one day, but people tend to input
the number and move on without wondering about an unusual result. On the
other hand, new employees receive on-the-job training, so 30 hours in a
single record is not beyond reason. If training records were entered every
day it may be possible to avoid that problem, but I'm not going to ask
people to enter five records where they are accustomed to entering one.
They have other things to do. The other thing that happens is that 30
minutes gets entered as .30. I ended up trapping those types of entries,
asking in the first case if they really mean 30 hours, and in the second if
they mean 30 minutes (.5 hours).
All I'm saying is that counting on users to assess the results of
calculations can be risky, especially if the users are functioning as data
entry people who have no real criteria by which to assess the results. You
as the developer are in a position to help people do their jobs by using
automation to question results that do not seem to make sense.
 
C

CJ

BruceM said:
The operative word here is "should". If the amount is expected to be
within a certain range you could have a message box appear if it falls
outside of that range. In a database used to log training the time is
entered in hours or decimal fractions (to the nearest quarter hour), so
that 30 minutes is .5. I have seen it entered as 30 hours in one day, but
people tend to input the number and move on without wondering about an
unusual result. On the other hand, new employees receive on-the-job
training, so 30 hours in a single record is not beyond reason. If
training records were entered every day it may be possible to avoid that
problem, but I'm not going to ask people to enter five records where they
are accustomed to entering one. They have other things to do. The other
thing that happens is that 30 minutes gets entered as .30. I ended up
trapping those types of entries, asking in the first case if they really
mean 30 hours, and in the second if they mean 30 minutes (.5 hours).
All I'm saying is that counting on users to assess the results of
calculations can be risky, especially if the users are functioning as data
entry people who have no real criteria by which to assess the results.
You as the developer are in a position to help people do their jobs by
using automation to question results that do not seem to make sense.
Click to expand...

Good point. I suppose a person needs to be mindful of what the weakest link
will do. In this instance we do have an input limit of about $0.10 so a
message box for any input over that amount will do the trick.
 
S

scubadiver

Automation is worth it since the data entry is less likely to produce errors.

After update event.

If [field] > 0.1 then MsgBox ("blah blah blah")

I think that should work.
 
Top