Form Calculations not returning correct results

[dwcrockford]

On a form, I have values based on a table lookup.
When I select "PERSON", the fields populate with correct values based on the associated table.

On that form, I also have values summing up criteria from a subform (associated to a separate table).
I have totals calculating properly regarding the subform. No issues as of yet.

Now, I want to do come "combo" calculations, using the values from the totals from the subform, and the values pertaining to the person's info.

I want to calculate value of hours.... So the code that's not working is:

=[TOTALHOURS] *[PAYRATE]

This refers to the names of the field boxes on the forms.

The TOTAL HOURS is calculated from the subform (and works correctly), the PAYRATE is pulled from the Employee Table, and the correct value is displayed for the correct individual.

However, when this "VALUE" field is calculated, instead of using the value of the PAYRATE, it is calculating based on the persons ID# (PK, autonumbered). So 10.00 hours x $35.00 returns a result of $20.00, which is because the persons ID# was 2.

I have played with bound column, etc., but am not getting the correct return.

Suggestions on what I'm missing in the coding?

D

 

[apr pillai]

How you are pulling payrate from Employees table?

 

[dwcrockford]

the source of the main form that contains the employee gets its info from a table (form designed from table), that gives the applicable details needed regarding the employee (name, address, payrate($), vacationrate(%))

 

[apr pillai]

the source of the main form that contains the employee gets its info from a table (form designed from table), that gives the applicable details needed regarding the employee (name, address, payrate($), vacationrate(%))

Open the Property Sheet of the Textbox, where the PAYRATE Value appears on the Form. Reconfirm that the Control Source Property and Name property values both refers to PAYRATE Field or not.

If the Name property value is different your calculation will not work.

 

[Micron]

Are you using a multi-column control such as combo box for one of these values? If so, and it has two or more fields in its underlying source, you are probably referencing the wrong column without realizing it. The visible data in a multi column control is not necessarily the data used - not if it is bound to a different field than the one you're looking at.
So if the underlying fields are ID and Rate (in that order), and you bind the control to column 0, you will return ID even though you see Rate. For multi column controls, the column order is zero based.

Sorry if I'm way off track, but in my defense, terminology is important. "field boxes" does not tell me what type of control you are using.

 

[dwcrockford]

Are you using a multi-column control such as combo box for one of these values? If so, and it has two or more fields in its underlying source, you are probably referencing the wrong column without realizing it. The visible data in a multi column control is not necessarily the data used - not if it is bound to a different field than the one you're looking at.
So if the underlying fields are ID and Rate (in that order), and you bind the control to column 0, you will return ID even though you see Rate. For multi column controls, the column order is zero based.

Sorry if I'm way off track, but in my defense, terminology is important. "field boxes" does not tell me what type of control you are using.

Thanks Micron..

That is exactly exactly the scenario that is going on, and I apologize for my incorrect terminology, old habits die hard.

The "field" to which I refer is in reality a combo box that "displays" the correct result (the pay rate) which is from a table. The properties for that combo box are indeed bound, and have changed the bound column number over and over. The display changes in terms of what is displayed in the combo box, however the calculation remains the same (hours x Id). Is there another spot I should be looking at?

 

[Micron]

The bolded words are properties you need to be concerned with.
To work correctly, a multi-column control needs a column count equal to the number of fields in the control's source. If you use a query that is too robust (brings back 10 and you only need 3 fields, you will still need 10 columns). To not show a field value (such as ID) set its width to 0 and bind the control to the column number that contains the field value you need to pass to code or a query. So to get PayRate from columns ID, LName, PayRate but see only last name, you ensure the column count is 3 and the widths are 0; 1.5; 0 (or whatever width values > 0 will work) and bind the control to column 2 (the third column). The control's list width will determine if all the columns are visible and horizontal scroll bars don't appear (assumning you've not disabled them). In my example, the list width need only be 1.5". Changing the bound column should not affect what you see in the control, so I suspect you may have more fields than you do columns.

If you can't figure it out now, post back with
- names of involved controls
- control source for those controls (a sql statement if applicable, or the table.field info for the control)
- if the control sources are fields from a form query, I may need the form's recordsource
- if the control is bound or unbound
- expression or code you're using to perform the calculation
- relevant properties for any combo box (or list box if that's what you're using); i.e. column count, bound column

P.S. your circumstance is also a reason not to have your control names the same as your field names, which is what a form/report wizard will do. Access will not always pick the control [Rate] if you have a field named [Rate]. I do use wizards for speed, but always rename my objects properly afterwards.

 

[dwcrockford]

Thanks, but it still doesn’t seem to work like it should – even following your directions. I will try to explain as best I can, but would be easier (for me) to include screen shots (which I don’t see the ability to add images in the forum that aren’t hyperlinks. So…. here goes.

Tables involved
Employees TBL (includes EEID, EmployeeName, Address, PayRate, VacRate (percentage), WorkHrBonus (yes/no))
tblTimesheetDetails (includes TimesheetID, Employee, StartPeriod, EndPeriod (calculated), Comments (particular to a specific timesheet) The Employee is linked (combo box lookup) to EmployeeName from Employees TBL.
CustomersTBL
tblTimsheetHoursWk1 (includes Customer (linked from CustomersTBL), Details, one “field” for each day of the week (Mon – Sun), TOTAL (calculated) based on each line entry (some customers have worked performed on more than one day).

Forms involved
frmTimesheetEntry – main entry form (the one having issues)
tblTimesheetHoursWk1 subform – created as an insertion on the main form.

So now onto the main form itself (frmTimesheetEntry).

Controls on main part of form:
Employee (taken from query of Employees TBL) – returns employee name correctly
PayPeriodStartDate (taken from tblTimesheetDetails) – displays correctly
PayPeriodEndDate (calculated from start date) – displays correctly
Address (taken from same query as above Employee)

The controls on the form having issues are:

Pay Rate
Name: EEPayRate
Type: Table/Query (combo box)
Control Source: Employee
Row Source: SELECT [Employees TBL].EmpID, [Employees TBL].PayRate FROM [Employees TBL];
Bound Column: 1
Column Count: 2
Column Widths: 0”;1”

This control displays the correct Pay rate for each employee (in currency)

Vacation Rate
Name: EEVacPayRate
Type: Table/Query (combo box)
Control Source: Employee
Row Source: SELECT [Employees TBL].EmpID, [Employees TBL].VacRate FROM [Employees TBL];
Bound Column: 1
Column Count: 2
Column Widths: 0”;1”

This control displays the correct Vacation rate for each employee (in percentage)

Total Hours
This is a calculated control which simply adds hours based from the subform
Name: TSTMonTotalHrs
Control Source: =([MondayTBHrs]+[MonTA])
(the above two control names referred in the calculation are working properly)

Regular Pay
Name: TSTMonRegPay
Control Source: =[EEPayRate]*[TSTMonTotalHrs]

When using the above control source calculation, the result is the Hours multiplied by the employee ID number instead of by the pay rate. The result is returned in currency format

Vac Pay
Name: TSTMonVacPay
Control Source: =[TSTMonTotalHrs]*[EEVacPayRate]

When using the above control source calculation, the result is the Hours multiplied by the employee ID number instead of by the vacation rate. The result is returned in currency format.

I hope I’ve covered all the info needed to determine how to change the calculated “results” to the correct values.

 

[Micron]

Wow! You really outdid yourself!
I think the problem is with the PaYRate combo. The control source is bound to the Employee field of your table/query while the rowsource is a query that brings back the pay rate for an employee, and you have designed to show the rate OK. I'm saying it's a conflict, but can't be positive because I'm assuming "Employee" is the id you're using but don't want. If I understand the need, you don't have to bind the control to the table/query because you're using this control to calculate - you don't want to be able to edit the field bound to the control. Try removing the control source but leave the rowsource. Sorry I cannot delve into it more at this time, but I wanted to take a quick look rather than make you wait until tomorrow. I'm tied up today.

Also, press F1 after selecting these two properties from the property sheet and see if the help provides more understanding. Maybe research rowsource, control source and record source.

 

[dwcrockford]

Well... the above didn't return the desired result. Removing the control source but leaving row source just returned an empty result in the form, without the ability to add or select anything. I did a work-around using and iif statement (iif [EmployeeName]=2,30) etc... with so few ee's to deal with, it is not a difficult work-around.... but would not be reasonable for a larger employee base.

D