Expression looking up null fields with totals

Bobbi

New member
Local time
Today, 06:45
Joined
Nov 15, 2000
Messages
8
I know one of you out there can help me with this. I posted yesterday, but hoping to find some help pretty fast.

Here's the deal. I have used this expression in another database and it works. But it won't in this one. The only thing I can think of, is the other database was created using Access 97 and I'm now using 2000. Heck, that might not be it, but it satisfies me so far. Anyway, what I'm trying to do is look up in a field and get the total contract price for all records that have no closing date. Maybe my formula will help to understand

=Abs(Sum([Contract Price]*(IsNull([Date Closed]))

Don't ask me how I finally figured it out for the existing database but I did, and it works perfectly.

What am I doing wrong?? I appreciate any help.

Bobbi
 
Iif (IsNull([DateClosed]),Sum([ContractPrice]),0)

[This message has been edited by Richie (edited 11-23-2000).]
 
Richie...I just tried that...it's not working for me. I get the same error. What is the "0" stand for? Cuz I need to tell it to only retrieve from certain fields. Since I'm getting so detailed, that is why I'm getting lost I think.

Thanks for your help!!
Bobbi
 
What error are you getting? The Iif statement evaluates a true / false part, If the condition is true the sum of your field will be returned, if false zero is returned, if you don't want zero's then use "" instead.
 
Richie....

With the exact expression you gave me, I'm getting a "#Name?" error. I think I understand the zero return, but I will be breaking it down even further. There are 3 phases (II, III, IV), I will add into the expression to only calculate all of this for only each individual phase. Will I still need the (0) or the ("")?

Bobbi
 
Iif (IsNull([DateClosed]),Sum
([ContractPrice]),0)

try this:
= Sum(Iif(IsNull([DateClosed]),0,[ContractPrice])

As for your phases, I assume these are recorded in three separate fields i.e. Phase1 complete:
Phase2 complete:
Phase3 complete:

the answer to that is to repeat the same formula only this time looking at the three different fields:

= Sum(Iif(IsNull([Phase1Complete]),0,[ContractPrice])
= Sum(Iif(IsNull([Phase2Complete]),0,[ContractPrice])
= Sum(Iif(IsNull([Phase3Complete]),0,[ContractPrice])

By definition, phase1 must have been completed before phase2 and phase2 must have been completed before phase3.

For completed contracts use:

= Sum(Iif(Not(IsNull([Phase3Complete])),[ContractPrice],0)

Hope that helps

Ian


[This message has been edited by Fornatian (edited 11-26-2000).]
 
Richie....

I don't want the sum of zero. Would this last string give me that? I was planning on trying the one Fornatian gave me later today.

When these expression get this complicated it loses me. I try to only think of one step at a time but I tend to overwhelm myself easily confusing myself.

Thanks for both of y'alls help. I'll post later the outcome.

Bobbi
 
Sorry,
I've come up with a problem doing it my way, because the total costs for projects only in Phase 1 will be included in BOTH phase 2 AND phase 3's unfinished project sums.

So what you'll need to do is include some more criteria for Phase 3 criteria to test a)that a phase3 is null and b)that phase2 isn't.

suchas:

= Sum(IIf(IsNull([Phase3])AND Not(IsNull([Phase2])),[ContractPrice],0)

ian

[This message has been edited by Fornatian (edited 11-26-2000).]
 
Ian...Don't be sorry for trying to help. I appreciate that very much! I did switch it and it still will not work for me.

So, I took the easy way out. I created a query to gather the information and in the report did a DLookup. I feel so cheap, but I need the numbers to work right now. This will give me time to figure the right way to do it.

Richie...I appreciate your help very much as well.

I have one last problem that I hope you will help me out with. I am done with the report other than getting a parameter value to ask for a date. I did it the way I know how....

In the report header, I created a text box. In the control source entered "Enter a desired date" without the quotations of course. It didn't work. I went to another report that I created a few months back and it's exactly like it. So, why won't it work in this one. The only thing different is that the older report was created using 97 and now I'm using 2000. I also tried to put brackets around the control source and still get the #Name? error. I've looked in the help files and to no avail. I must have used up all my brain cells for Access, either that or it's my kids constant interruptions.

Thanks again and I hope you can help me with this latest one.

Bobbi
 
Why don't you base the report on the query? Add the date criteria to the query.
 
Richie...I'm not sure I follow what you mean by using the query for the date.

Are you suggesting that the query I created for this last problem of mine, that I have criteria in one of the sources be an ending/desired date?

Bobbi
 
If your report is based on a query which includes a date field then just add Between [StartDate] And [EndDate] to the criteria in the criteria grid for that field, in the parameters box enter [StartDate] Date/Time [EndDate] Date/Time, providing the reports record source is the query it will work.
HTH
 
I hate to state the obvious, but your initial example has mismatched brackets. You have 4 opening brackets, but only 2 closing brackets.

You close the "IsNull" function, and then the multiplication, but not the SUM nor the ABS.

=Abs(Sum([Contract Price]*(IsNull([Date Closed]))
 
I hate to state the obvious, but your initial example has mismatched brackets. You have 4 opening brackets, but only 2 closing brackets.

You close the "IsNull" function, and then the multiplication, but not the SUM nor the ABS.

=Abs(Sum([Contract Price]*(IsNull([Date Closed]))
 

Users who are viewing this thread

Back
Top Bottom