Overide calculated control field

[vbaInet]

What number is that? 3?

In the control source property, select from the list the new CommissionDue field.

 

[louisa]

No im only on number 2, i have been reading about queries, still not quite sure about which fields i need to include or how to get it to halve the value,

 

[vbaInet]

Use the query wizard, pull in all the fields that are present in your form.

Once you've done that, right-click the query (i.e. the grey area) and select SQL View. Copy and paste that here.

 

[louisa]

SELECT Commission.TotalKitCost, Commission.ContactID, Commission.EngineeringCost, Commission.OtherCharges, Commission.Specify, Commission.Settlements, Commission.Specify1, Commission.GrossOperatingProfit, Commission.SalesRep, Commission.TelesalesRep, Commission.NetFigure, Commission.CommissionDue, Commission.Invoiced, Commission.InvoiceNo, Commission.Amount, Commission.Paid, Commission.Paid1, Commission.Notes, Commission.PaidDate, Commission.Maintained, Commission.Maintained1, Commission.MaintenanceCharge, Commission.Total, Commission.ContractPeriod, Commission.TelesalesCommission, Commission.TeltecCharge, Commission.TotalInvoiceAmount, Commission.DepositPaid, Commission.DepositPaid1, Commission.DepositAmount, Commission.CommissionPaid, Commission.CommissionPaid1, Commission.CommissionPaid2, Commission.CommissionPaid3, Commission.DatePaid, Commission.DatePaid1, Commission.CommissionDue1, Commission.Paid2, Commission.Paid3, Commission.DatePaid2, Commission.InvoiceReceived, Commission.InvoiceReceived1, Commission.InvoiceReceived2, Commission.CommissionDue2
FROM Commission;

 

[vbaInet]

Copy and paste this (i.e. in the SQL view) into a new query:

UPDATE Commission 
SET CommissionDue = GrossOperatingProfit / 2;

Once pasted, switch back to design view just to see what it looks like (for next time). You would obviously need to amend the field names where necessary.

 

[DCrake]

You seem to be saving alot of repetative fields 1,2 & 3 in your table. You really need to put them in another table and create a relationship betwen the two using the primary key from the commission table to the many table. What happend if you need to increate it to 4 or 5?

 

[louisa]

I have done as you advised, however, my gross operating profit field appears to be bringing up a 0 value, would this be because that field is also has a calculated contol source?

 

[vbaInet]

Did you perform this on a backup? You mentioned GrossProfit is a calculated, what is the calculation? That would be your culprit because what I advised won't have affected that control.

 

[louisa]

Yes it is on a backup with dummy data inside. The GrossOperatingProfit Formula is =Nz([Amount],0)+Nz([DepositAmount],0)-Nz([Total],0)

 

[vbaInet]

I would advise you break down your calculation into bits. If the Amount, DepositAmount and Total fields are not in the recordsource then it will result in a zero.

 

[louisa]

Sorry i do not understand what you mean?

 

[vbaInet]

Check if all three fields are returning data.