Newbie Problem with Formulas

[Argonaught]

I am quite new to Access but have made myself a basic database which works fine.
The problem arises now i am trying to get it to do things i have previously done in excel.
Anyway the problem is this, i have 3 fields, "Paid For", "Sold For", and "Profit".
All i want to do is deduct the "Sold For" field from the "Paid For" field and display it in the "Profit" field.
The very last part of my problem is that i wish to have a "Total Profit" field to show the combined profits.

Below are the formula i have tryed without success:

=IF (sum ([Sold For])>0, Sum([Sold For]-[Paid For])

The second formula i tryed to shorten it:
=Sum ([Sold For]-[Paid For]) this works, but i get the same value in all my records and i need it to give a different result for each record.

I have no idea how to total all profits!

If anyone can help me it would be very much appreciated.

Cheers JA. aka Argonaught

 

[GizmoT]

Argonaught

First part of the problem is easy.

The control for your form field Profit should be:



=[Sold For]-[Paid For]

 

[Argonaught]

This places the answer in all the profit fields on all the records.

 

[jfgambit]

You should not be storing calculated fields in a database. It requires additonal coding when chages are made. Here is a down and dirty example for how to accomplish what you are looking for.

 

[cominapart]

Argonaught I am a newbie also but maybe this can lead you in the right direction.


If your fields go like this: SoldFor - PaidFor = Profit


In the AfterUpdate property of the PaidFor Field add this:

Me.Profit = [SoldFor] - [PaidFor]

Then in the next to last filed before the Total field try again in the
AfterUpdate Property:

Me.NextToLastField = [PaidFor1] + [PaidFor2] ....

hth

 

[cominapart]

sorry for butting in. I didn't see jfgambits reply i was trying to figure this out at the same time.

 

[jfgambit]

Don't worry about it, everyone is here to help. But I need to stand by my original quote, "You should not store calculated fields in a table."

All calculations, whether Total Gross Margin, total Profit for particular items, Total Sales, etc...can all be accomplished from your SoldFor and PaidFor fields. Storing the calculations is going to require you to add coding to update the fields once changes have been made. And this can cause headaches for you.

Take a look at the sample, the calcualtions are all done on the form and not stored in the tblItems table.

HTH

 

[cominapart]

Thanks jfgambit, i took a look at the forms design. You are
right, i don't need any more work than necessary, especially some i would create myself from not doing something right the first time!

 

[Argonaught]

Cheers for the help im going to try the suggestions out and ill let you know.

 

[Argonaught]

cheers jfgambit that works, but.. for if i dont put a sold price in i get a negative value, i want it to ignore the formula if no value is entered in the Sold For field.
The example you sent me works perfectly but when i copied the formula into my database i get negative values!!

Any ideas? I will continue comparing differences
If you dont respond thank you for the help so far.

Cheers Argonaught

 

[jfgambit]

Change the Control Source of the textbox from =[SoldFor]-[PaidFor]

to =IIf([SoldFor]=0,0,[SoldFor]-[PaidFor])

HTH

 

[Argonaught]

Cheers, all sorted and working a treat.

Thanks alot