Formula help

[Chad]

Hello, I was woundering if this is possible and if so how would I go about
doing it. I have a text box named txtEmployeeTime that we enter how many
hours in decimals an employee worked for the day (Never exceeds 8.00). I also
have a text box named txtDTRegular which we enter that employees machine
delay time in decimals. Ok, now here is what I want to do, I want to have a
text box where I enter the employees "run time" minutes (We get the number
from a PC thats using a PLC) and it will subtract that from txtEmployeeTime
and give you a total in txtDTRegular in decimals. I am saving the info in the
control sources txtEmployeeTime and txtDTRegular to fields in the forms
table. Thanks!

 

[Scott]

What? I've read this four times and it seems very circular. You enter data
in a text box named txtDTRegular but you also want to have results appear in
that same text box?

 

[Chad]

Here is how its supposed to work but the VBA is messed up.
I enter a number into txtMinutes and that number is suposed to be subtracted
from the number already in txtAccTimeWorked and then give the total in
another text box named txtDTRegular.

Private Sub txtMinutes_AfterUpdate()
Me.txtDTRegular = (Round(Me.txtMinutes / 60, 2) - Me.txtAccTimeWorked)
Forms!frmMainDB!txtMinutes = Me.txtDTRegular
End Sub

 

[Ken Sheridan]

You don't need to assign the value to txtDTRegular unless you want to be able
to edit the value assigned. You can make the ControlSource of txtDTRegular:

= Me.txtAccTimeWorked – Round(Nz(Me.txtMinutes,0) / 60, 2)

In this scenario you should not be storing the value in txtDTRegular in a
column in the underlying table, but only the values of txtAccTimeWorked and
txtMinutes, which should be bound controls, as the value of txtDTRegular is
computed from these.

If you do want to be able to edit the value of txtDTRegular then assign it
in the txtMinutes control's Afterupdate event procedure using the same
expression. In this scenario all three text boxes would be bound controls
and you'd need three columns in the table.

What is the following meant to do?

Forms!frmMainDB!txtMinutes = Me.txtDTRegular

At first sight it seems to be assigning the computed value back to
txtMinutes, which does not seem very logical.

Ken Sheridan
Stafford, England

 

[Chad]

Ken, I have this posted at Access Forum as well as here so I can upload a
test file so people to look at. You could understand my post better if you
see the test DB, the post and DB are here....
http://www.access-programmers.co.uk/forums/showthread.php?t=141599

 

[Ken Sheridan]

In your form the AfterUpdate event procedure of the Text2 control would
contain the following code:

txtDTRegular = Round((Text2 / 60) - (txtEmployeeTime - txtDTReason1), 2)

But first you need to change the data types of the three columns (apart from
the ID column) in the table to single precision floating point numbers. At
present they are long integers so the delay result will be rounded to the
nearest hour.

I still feel you'd be better storing values *entered* in the form and
computing the delay, however.

Ken Sheridan
Stafford, England

 

[Chad]

Ken thanks, It works but its giving me an negative number? Like if my ending
result was .33 it would show -.33? Thanks! Also what do you mean by saving?
Could you explain since you think it is a better solution I might want to go
with it......

 

[Chad]

Ken, I got the minus symbol. I used this formula instead and it works great!
Thanks for all your help...

Me.txtDTRegular = Round((txtAccTimeWorked) - (txtMinutes / 60), 2)