Hi
i have a query where I am calculatimg the totals based on the following formula

([Prime Rate]*[Prime Spots Posted])+([Rot Rate]*[Rot Spots Posted])+ ([Other Rate]*[Other Spots Posted]) AS [Actual Total]

My issue is.... i only want to add the values to the [Actual Tota] if BuyStatus is not = 'PASS' or 'Re-Negotiate'

But this query is also used to display records in a form/subform.
I still want to see the records where BuySTatus = Pass or Re-Negotiate, I just don't want them added into the totals,,
can anyone help me with this?




SELECT WBQ.STATION, WBQ.[Buy Status], WBQ.Keep, WBQ.MISC, WBQ.[Format ID], WBQ.[Station Rep], WBQ.[Market ID], WBQ.[Rep ID], WBQ.[# Prime Spots], WBQ.[# Rot Spots], WBQ.[# Other Spots], [Prime Rate]*[# Prime Spots]+[Rot Rate]*[# Rot Spots]+[Other Rate]*[# Other Spots] AS Total, M.[State ID], WBQ.[Other Rate], WBQ.[Rot Rate], WBQ.[Prime Rate], WBQ.[Campaign ID], WBQ.[Station Comments], WBQ.[Buy Status Comments], WBQ.[Prime Spots Posted], WBQ.[Rot Spots Posted], WBQ.[Other Spots Posted], WBQ.M, WBQ.T, WBQ.W, WBQ.TH, WBQ.F, WBQ.[Total Calls], [Prime Rate]*[Prime Spots Posted]+[Rot Rate]*[Rot Spots Posted]+[Other Rate]*[Other Spots Posted] AS [Actual Total], WBQ.[Source Num], WBQ.[Week Start Date], WBQ.[Opt In], WBQ.Wk1, WBQ.Wk2, WBQ.Wk3, WBQ.Wk4, WBQ.Wk5, WBQ.Wk6, WBQ.[Campaign Comments], WBQ.[Day Part Primary], WBQ.[Day Part Rotator], WBQ.[Day Part Other], WBQ.[Full Name], WBQ.[Contact Full Name from Rates]
FROM [Weekly Buy Query] AS WBQ LEFT JOIN [MARKETS TABLE] AS M ON WBQ.[Market ID] = M.[Market ID]
WHERE (((WBQ.Keep)=Yes) AND ((WBQ.[Campaign ID])=[Forms]![Maintain Products Form]![Campaign ID]) AND ((WBQ.[Week Start Date])=[Forms]![Weekly Buy Table Form]![Week Start Date]));

If I've understood this correctly then you could add a calculated field to your query with an IIF statement in it and pickup on that column in the parent form.

Alternatively you could keep a tempory table with all the output fields from the query + a y/n field
- reload from your query and then run an update query (with an IIF in it) to set the y/n field dependent upon the 'BuyStatus ' and feed the form from this recordsource.

I offer the latter as your SQL could get rather large and ugly wth the IIF added to it and could result in Access issuing an error that the SQL is too complex.

thanks, i think id rather try the first suggestion and see if access can handle it,
bc the 2nd suggestion sounds really complicated and i would probably mess it up.

I am VB challenged, could you possibly help me out with the actual vb code

I would encourage you to consider changing how you name your objects, when the time is appropriate.

They should not include spaces or special characters. Just jam all of the text together ...PrimeSpotsPosted, StationRep, etc. It will save you time and debugging effort later in the development cycle.

In regards to your original question, a simple IIF() should do it. Something like:

IIF(BuyStatus IN('PASS','Re-Negotiate'), 0, ([Prime Rate]*[Prime Spots Posted])+([Rot Rate]*[Rot Spots Posted])+ ([Other Rate]*[Other Spots Posted])) AS [Actual Total]

Ooh that worked, beautiful, thanks so much,,, now i realized i have another issue

fyi,, i changed the names of my fields per your suggestion:

i also use the same fields in a calculated field on the parent form

the query you helped me modify is on a subform,, and the calculated field is on the parent form


DiffTB is the name of the calculated field
=[WeeklyBudget]-Sum([PrimeRate]*[PrimeSpots]+[RotRate]*[RotSpots]+[OtherRate]*[OtherSpots])


I also don't want to include the records where BuyStatus = Pass or Re-negotiate in the Total Difference


This Difference(DiffTB) is actually the Difference between the Weekly Budget and the ActualTotal that you helped me calculate,,


any suggestions?



between the

If I understand you correctly, you should be able to use the same formula:


=[WeeklyBudget]-Sum(

IIF(BuyStatus IN('PASS','Re-Negotiate'), 0, ([Prime Rate]*[Prime Spots Posted])+([Rot Rate]*[Rot Spots Posted])+ ([Other Rate]*[Other Spots Posted]))

)

yup, that worked too!!!
THANKS SO MUCH, Really appreciate your help!