Anyone Help With SUM IIF Statement. 1

[anhfuego1]

I created an IIF statement formula that pulls data if it meets the criteria. If data is null in the Plan
Circ(Text285) then it pulls data from Circulation(Circulation) into a 'make-shift' cell named CircForPlanCalcs: The IIF works fine, it's just that I need to SUM up the pulled data. Everytime I try to do this, Access reads the Sum(CircForPlanCalcs) formula as a parameter query.

CircForPlanCalcs =IIf([Text285] Is Null,[Circulation],[Text285])

is the formula.

Detail area: Text285, Circulation
Footer: I need the Sum of CircForPlanCalcs.

How do I sum up CircForPlanCalcs? Could it be a circular issue?

Any help would be greatly appreciated!

 

[Trevil]

I had posted a response to your other posting at:

http://www.tek-tips.com/viewthread.cfm?qid=1091658&page=1

Was that useful?

"Hmmm, it worked when I tested it....

 

[anhfuego1]

Trevil, in response, I tried to that Formula and it doesn't work. Access still recognizes it as a parameter query.

Any other suggestions would be awesome, thanks.

 

[Trevil]

Do you have a query for the report Record Source? If so, is there a reason that you can't place your IIF there?

If you can do the IIF in your query, then my suggestion should work. For anyone else reading this, the following are comments from the other thread:
Using fields from the recordsource would allow you to use:
=Sum(IIf([fld1] Is Null,[fld2],[fld3])



"Hmmm, it worked when I tested it....

 

[anhfuego1]

Yes I do have a query for the record source. the data streams in through a view table on the network here. I tried to place my IIF there but the results came back null.

I still can't find the correction =(

 

[Trevil]

Can you post the SQL that has your IIF in it? It may be something simple.

"Hmmm, it worked when I tested it....

 

[anhfuego1]

SELECT "" AS [Desc], dbo_vwProjectedDemand.CustomerProspect, dbo_vwProjectedDemand.CustomerClass, dbo_vwProjectedDemand.ProspectClass, dbo_vwProjectedDemand.Category, dbo_vwProjectedDemand.CategoryDesc, dbo_vwProjectedDemand.GroupValue, dbo_vwProjectedDemand.ActualMailDate, dbo_vwProjectedDemand.EffortCode, dbo_vwProjectedDemand.Circulation, dbo_vwProjectedDemand.DaysOut, dbo_vwProjectedDemand.EstRR AS [Plan RR], dbo_vwProjectedDemand.EstAOV AS [Plan AOV], dbo_vwProjectedDemand.EstRevenue AS [Plan Rev], [Orders]/[Circulation] AS [Actual RR], dbo_vwProjectedDemand.Orders AS [Actual Orders], [Revenue]/[Orders] AS [Actual AOV], dbo_vwProjectedDemand.Revenue AS [Actual Revenue], [Revenue]/([Circulation]/1000) AS [Actual Rev/M], dbo_vwProjectedDemand.[%Thru], dbo_vwProjectedDemand.ProjectedRevenue AS [F/cast Final Rev], dbo_vwProjectedDemand.ProjectedOrders, dbo_Date.FiscYear AS [Year], dbo_Date.MonthNameFull AS [Month], dbo_vwProjectedDemand.Format, dbo_vwProjectedDemand.ProjectName, dbo_vwProjectedDemand.OfferType, dbo_vwProjectedDemand.ListTypeDesc, dbo_vwProjectedDemand.JobTypeDesc, dbo_vwProjectedDemand.EmplSizeStart, dbo_vwProjectedDemand.EmplSizeStop, dbo_vwProjectedDemand.AudienceTypeDesc, dbo_vwProjectedDemand.NewAudienceConcat, dbo_vwProjectedDemand.OfferGroup, dbo_vwProjectedDemand.PlanCirculation INTO [Report Data 1]
FROM dbo_vwProjectedDemand INNER JOIN dbo_Date ON dbo_vwProjectedDemand.ActualMailDate = dbo_Date.Date
WHERE (((dbo_vwProjectedDemand.ActualMailDate) Between [Start Date] And [End Date]) AND ((dbo_Date.MonthNameFull)=[What Month? Spell Out]) AND ((dbo_vwProjectedDemand.PlanCirculation)=IIf([PlanCirculation] Is Null,[Circulation],[PlanCirculation])))
ORDER BY dbo_vwProjectedDemand.CustomerProspect, dbo_vwProjectedDemand.CustomerClass, dbo_vwProjectedDemand.ProspectClass, dbo_vwProjectedDemand.EffortCode;

 

[Trevil]

Your SQL does not return the 'answer' because the IIF that you have is in the 'WHERE' portion and not in the 'SELECT' portion. You could use aomething like:
select ....flda, IIf([PlanCirculation] Is Null,[Circulation],[PlanCirculation]) AS MyNbr from ...
to return the non-null field.

Was you existing IIF where you wanted it?

"Hmmm, it worked when I tested it....

 

[anhfuego1]

Truthfully, I thought that I could just sum up the fields in the report and not have any criteria in the query. But that proved invalid. I am not well versed in SQL to understand what you meant w/ your last post.

Please clarify. thanks!

 

[Trevil]

GULP! Now that I deleted the query I was testing with, let me try to explain (with a disclaimer of course):

I will assume the IIF that was in your original SQL was not needed for record selection criteria, but was there in an attempt to solve the problem you are having.

Using your original SQL:
1. Delete the syntax where you have your IIF.

2. Replace the portion that is:
dbo_vwProjectedDemand.PlanCirculation INTO [Report Data 1]

with:
dbo_vwProjectedDemand.PlanCirculation, IIf([PlanCirculation] Is Null,[Circulation],[PlanCirculation]) AS MyNbr INTO [Report Data 1]

The output from this query will now have a non-null value returned in a new field named 'MyNumber' (this assumes that if Circulation is NULL, then PlanCirculation will NEVER be null)

3. Open your form and in the detail section where you had your control, change the control source to 'MyNbr'. Let's say that control is named 'Text285'

4. In the footer (or wherever you want the SUM), place your control and set the control source to '=Sum([Text285])'

Let me know if this does the trick....


"Hmmm, it worked when I tested it....

 

[anhfuego1]

I did what you suggested, but a prompt pops up stating that I cannot define a field more than once. What to do now?

 

[Trevil]

That error is caused by your selecting the same field twice.

Since I don't have your latest SQL, you can test by deleting half of your fields, then try to run the query. If problem goes away, go back to original query and delete one-forth, etc. until you isolate the guilty party.

"Hmmm, it worked when I tested it....

 

[anhfuego1]

SELECT "" AS [Desc], dbo_vwProjectedDemand.CustomerProspect, dbo_vwProjectedDemand.CustomerClass, dbo_vwProjectedDemand.ProspectClass, dbo_vwProjectedDemand.Category, dbo_vwProjectedDemand.CategoryDesc, dbo_vwProjectedDemand.GroupValue, dbo_vwProjectedDemand.ActualMailDate, dbo_vwProjectedDemand.EffortCode, dbo_vwProjectedDemand.Circulation, dbo_vwProjectedDemand.DaysOut, dbo_vwProjectedDemand.EstRR AS [Plan RR], dbo_vwProjectedDemand.EstAOV AS [Plan AOV], dbo_vwProjectedDemand.EstRevenue AS [Plan Rev], [Orders]/[Circulation] AS [Actual RR], dbo_vwProjectedDemand.Orders AS [Actual Orders], [Revenue]/[Orders] AS [Actual AOV], dbo_vwProjectedDemand.Revenue AS [Actual Revenue], [Revenue]/([Circulation]/1000) AS [Actual Rev/M], dbo_vwProjectedDemand.[%Thru], dbo_vwProjectedDemand.ProjectedRevenue AS [F/cast Final Rev], dbo_vwProjectedDemand.ProjectedOrders, dbo_Date.FiscYear AS [Year], dbo_Date.MonthNameFull AS [Month], dbo_vwProjectedDemand.Format, dbo_vwProjectedDemand.ProjectName, dbo_vwProjectedDemand.OfferType, dbo_vwProjectedDemand.ListTypeDesc, dbo_vwProjectedDemand.JobTypeDesc, dbo_vwProjectedDemand.EmplSizeStart, dbo_vwProjectedDemand.EmplSizeStop, dbo_vwProjectedDemand.AudienceTypeDesc, dbo_vwProjectedDemand.NewAudienceConcat, dbo_vwProjectedDemand.OfferGroup, dbo_vwProjectedDemand.PlanCirculation, dbo_vwProjectedDemand.PlanCirculation, IIf([PlanCirculation] Is Null,[Circulation],[PlanCirculation]) AS MyNbr INTO [Report Data 1]
FROM dbo_vwProjectedDemand INNER JOIN dbo_Date ON dbo_vwProjectedDemand.ActualMailDate = dbo_Date.Date
WHERE (((dbo_vwProjectedDemand.ActualMailDate) Between [Start Date] And [End Date]) AND ((dbo_Date.MonthNameFull)=[What Month? Spell Out]))
ORDER BY dbo_vwProjectedDemand.CustomerProspect, dbo_vwProjectedDemand.CustomerClass, dbo_vwProjectedDemand.ProspectClass, dbo_vwProjectedDemand.EffortCode;

 

[anhfuego1]

Thanks for all of your help Trevil! You ROCK!

 

[Trevil]

Not sure if you figured this out yet, but you have selected 'PlanCirculation' twice.

(see ', dbo_vwProjectedDemand.PlanCirculation, dbo_vwProjectedDemand.PlanCirculation, IIf....)

"Hmmm, it worked when I tested it....

 

[anhfuego1]

Yeah, I got it. thanks again for all of your advice.