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

  • Thread starter Thread starter rob mccoy via AccessMonster.com
  • Start date Start date
R

rob mccoy via AccessMonster.com

Hello- I'm working on an employee record database with several subforms. One of the subforms keeps track of occurrence info. The fields in the occurrence subform are : Last Name( links subform to main form ) , # of occurrences, date occurences recieved, and total occurrences in the last 12 months. I can't figure out what the code should look like for the control source of the [# of occurrences in the last 12 months] field to make it perform that calculation. That is - only sum the data in the [# of occurrences] field based on the data in the [date occurrences recieved] field - if it is within the last 12 months. Any help would be greatly appreciated. Thanks...
 
Rob,

You can't do a calculation in a field.

If I understand you correctly, this subform can have a number of
records, one for each occurences date, right? So I guess this is a
continuous view form, right? But you only need one place where you see
the [# of occurrences in the last 12 months], right? Well if you have a
field for this, I suggest you remove it. Instead, you can put an
unbound textbox in the Form Footer section of the subform, and set it's
Control Source to something like this...
=Sum(IIf([date occurrences received]<DateAdd("yyyy",-1,Date()),0,[# of
occurrences]))

A couple of other "by the way" comments, if I may...
- it is not a good idea to put a # as part of the name of a field or
control.
- linking a subform to a main form on a [Last Name] field assumes there
will never be more than one employee with the same Last Name, and for
this reason would not normally be a recommended practice.
 
Steve,

Thank you. Thank you. Thank you. ...sigh...

The code worked great, and the tips are appreciated. If the subforms were linked to the main form by first AND last names, in separate fields, do you forsee any problems? thanks again...

....rob
 
Rob,

Well, using Last Name and First Name would certainly reduce the chances,
but the principle remains. There are a lot of John Smiths out there!
The usual approach would be to use something that will always be unique.
If the organisation does not allocate some sort of Employee Number or
some such, then you cna use an AutoNumber field in the Employee table to
create an arbitrary ID... that's what I would do.
 
If the subforms were linked to the main form by first AND last names, in separate fields, do you forsee any problems?
Click to expand...

Certainly, you will have problems. Names ARE NOT UNIQUE. I know three
people named Fred Brown, right here in little Parma, Idaho; and I once
worked with Dr. Lawrence David Wise and his colleague, Dr. Lawrence
David Wise. Names should never, ever be used as primary keys or
linking fields.

John W. Vinson[MVP]
 
steve,
thanks again for your help. i took your advice and linked the forms based on an employee number, which is unique to each employee. Hopefully this eliminates the chance of screwing up the records. Another question: I've got a subform for payroll - contains the following fields: [in time] , [out time] , [lunch] , [total time](for the day), and on the footer-[total time for the week]. When we turn in payroll to our corporate office- we base times on .1hr (=6 min), so say jon smith worked 8 1/2hr it would be 8.5 - so, how to calculate [total time](for the day) based on [in time], [out time], - [lunch] ,and display the results in the decimal format? Thank you once again for your help...


....rob
 
Rob,

Assuming that [in time] and [out time] are both Date/Time data type
fields, and that [lunch] is a Number data type, try the following as the
Control Source of an unbound text box in the footer of the subform...
=Round(Sum(([out time]-[in time])*24-[lunch]),1)
 
Steve,
I tried the expression that you gave me - the value that it returns is : #name?
the format for both[in time] and [out time] is set at date/time;medium time. The format for lunch is standard number. The Fomat for [total daily time] is standard number and it's in the footer. thanks for all your help...


....rob
 
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
 
Rob,

You mean in the footer of the subform, not the footer of the main form,
right? If so, 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.

As for [total daily time], you mentioned this is a field. This should
not be a field, and I would recommend you remove it from the table. I'm
sorry, I overlooked responding to this in my earlier reply. You can put
an unbound control for this in the Detail section of the subform, and
the Control Source expression to show the daily hours is similar to the
one for the weekly, just without the Sum()...
=Round(([out time]-[in time])*24-[lunch],1)
 
Steve said:
... 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.
Click to expand...

.... or that you're using a version of Access prior to Access 2000, in
which case the Round() function will not be recognised.
 
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,
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?
 
Rob,
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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?
Click to expand...

Well, I still don't know what the problem is, but Relationships are not
relevant.
 
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
 
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,

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
 
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,
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
 
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]))
 
Back
Top