Field calculation based on data in separate fields within the same subform...

Steve Schapel wrote:

... the most likely cause of the problem is a spelling error.
Or, the unlikely event that the name of the textbox in question is the
same as the name of a field in the subform's record source.

.... or that you're using a version of Access prior to Access 2000, in
which case the Round() function will not be recognised.

--
Steve Schapel, Microsoft Access MVP

Rob,

This would indicate that the lunch textbox is not bound to a field in
the form's underlying table/query. Do you have a lunch field in the
table? If so, make sure that the Control Source property of the textbox
on the form is set to point to the lunch field.

--
Steve Schapel, Microsoft Access MVP


rob mccoy via AccessMonster.com wrote:
Steve, another thing that is happening is that when I enter data into
the [in time] [out time] fields -those values only appear in the form
representing that particular day, but when I enter data in to the
[lunch] field-that value carries over into all of the other
forms/records in the subform...

...rob

Steve,
OK - The problem with the [lunch] field displaying in all records was an easy fix,but I still can't get the payroll subform to work. This is what I'm working with: Main form contains a subform called [payroll]; which contains a nested subform called [pay period details]; which contains the following fields:[in time], [out time], [lunch], and [Date](this last field doesn't use date() or anything, just manual data entry). Also in the Details area of [pay period details] is an unbound text box called[total daily time]- it uses the expression that you suggested to calculate daily time as it's control source. -oh- i have the unbound text box called [total weekly time] in the footer section of the subform itself ([payroll])- it uses the expression that you suggested to calculate weekly time as it's control source. I'm using Access 2002, here are the problems i'm still having:
1. when i enter a .8(this is the most common lunch length) in the [lunch] field - the field automatically turns the .8 into a 1.
2. the [total daily time] text box returns #name? no matter what data is in the fields that it calculates. i checked everything for spelling errors and couldn't find any.
3. the [total weekly time ] text box returns #error?
I think I might just delete this payroll form(and table) completely and build it from scratch. when i initially created it I added and removed elements as afterthought. Maybe my relationships are screwed up?

--
Message posted via http://www.accessmonster.com

Rob,

rob mccoy via AccessMonster.com wrote:

1. when i enter a .8(this is the most common lunch length) in the [lunch] field - the field automatically turns the .8 into a 1.

Go the the design view of the table, select the lunch field, and look at
the Field Size property. I guess it is set to Integer or Long Integer.
In fact, the best approach in this case is to set the Data Type to
Currency and the Format property to General Number.

2. the [total daily time] text box returns #name? no matter what data is in the fields that it calculates. i checked everything for spelling errors and couldn't find any.

We'll crack it eventually! Just as an experiment, try it without the
Round() function, i.e.
=([out time]-[in time])*24-[lunch]
If that doesn't give a "real" answer, try it simply as...
=[out time]-[in time]
Let me know.

3. the [total weekly time ] text box returns #error?

This will need to be in the Footer of [pay period details], not [payroll].

I think I might just delete this payroll form(and table) completely and build it from scratch. when i initially created it I added and removed elements as afterthought. Maybe my relationships are screwed up?

Well, I still don't know what the problem is, but Relationships are not
relevant.


--
Steve Schapel, Microsoft Access MVP

Steve,
I tried something- instead of manually entering the expression- i used the expression builder to "build" the expression you suggested:=Round(([out time]-[in time])*24-[lunch],1) for calculating daily time - it worked- well kind of worked - it will do a calculation, but if a 2nd shift person works past midnight , or more than 12 hours - it returns negative values, or wrong values. for instance: if i input an [in time] of 6pm and an [out time] of 12am with a .8 [lunch] - it returns a value of 17.2, or 7pm- 9pm returns -2.
I used the expression builder to duplicate the expression for calculation total weekly hours too:=Round(Sum(([End Time]-[Start Time])*24-[Lunch]),1) it returns the proper numerical values except that they are negative...

suggestions?

thanks,

rob

--
Message posted via http://www.accessmonster.com

Rob,

Are the actual names of the fields [End Time] and [Start Time] rather
than [out time] and [in time]? This would certainly explain the
previous error!

Regarding the negative value, could it be possible that you have the
controls accidentally reversed on the form, so that the worker's start
time is being entered into the [End Time] field and vice versa?

To handle the fact that the work "day" may span across midnight,
probably the simplest approach is to have the [End Time] and [Start
Time] fields to include a date component as well as a time component, or
else to have additional fields for [End Date] and [Start Date]. Is this
feasible? Otherwise, it is a bit complicated, but it would be possible
to put together a "user-defined function" to work this out for you.

--
Steve Schapel, Microsoft Access MVP

rob mccoy via AccessMonster.com wrote:
Steve, I tried something- instead of manually entering the
expression- i used the expression builder to "build" the expression
you suggested:=Round(([out time]-[in time])*24-[lunch],1) for
calculating daily time - it worked- well kind of worked - it will do
a calculation, but if a 2nd shift person works past midnight , or
more than 12 hours - it returns negative values, or wrong values. for
instance: if i input an [in time] of 6pm and an [out time] of 12am
with a .8 [lunch] - it returns a value of 17.2, or 7pm- 9pm returns
-2. I used the expression builder to duplicate the expression for
calculation total weekly hours too:=Round(Sum(([End Time]-[Start
Time])*24-[Lunch]),1) it returns the proper numerical values except
that they are negative...

suggestions?

thanks,

rob

Steve,

The field names were [in time] [out time] , and thatwas how they were represented in the expression , but when you suggested a spelling error - i went back thrugh all the forms, subforms, and underlying tables and in the process of doing so changed those field names.

About the field controls for [start time] [end time]- I'm not sure what you mean - I'm just manually entering the data into these fields. Is there a way to add a date tag to these fields?

Anyway Steve-I really appreciate all of the help. Thanks again...\

....rob

--
Message posted via http://www.accessmonster.com

Rob,

A Date/Time field can have a date entered, a time entered, or a date and
time entered. At the moment you are only entering the time, but with no
change at all to your system (except possibly making the textbox on the
form wider!), you can enter like this...
1/16/05 9:00
.... and that way your calculations will take account of the midnight
factor, because the [End Time] will be the following date.

--
Steve Schapel, Microsoft Access MVP


rob mccoy via AccessMonster.com wrote:
Steve,

The field names were [in time] [out time] , and thatwas how they were
represented in the expression , but when you suggested a spelling
error - i went back thrugh all the forms, subforms, and underlying
tables and in the process of doing so changed those field names.

About the field controls for [start time] [end time]- I'm not sure
what you mean - I'm just manually entering the data into these
fields. Is there a way to add a date tag to these fields?

Anyway Steve-I really appreciate all of the help. Thanks again...\

...rob

Steve,
Hey - thanks for that last tip. it works great.

how do you write an expression that gives the intruction to Sum a field if the data entered into another field is a particular text value.
this is what i tried: =sum(IIf[field name]="text"),0,[field being summed]))

It doesn't work.

thanks again...


....rob

--
Message posted via http://www.accessmonster.com

Rob,

Looks like the general idea is right, but parentheses slightly
misplaced. Try it like this...
=Sum(IIf([field name]="text",0,[field being summed]))

--
Steve Schapel, Microsoft Access MVP

rob mccoy via AccessMonster.com wrote:
Steve,
Hey - thanks for that last tip. it works great.

how do you write an expression that gives the intruction to Sum a field if the data entered into another field is a particular text value.
this is what i tried: =sum(IIf[field name]="text"),0,[field being summed]))

It doesn't work.

thanks again...


...rob