total amount

[sdc1234]

Greetings,

I am trying to calculate that when students are staying on campus it is mulitplied by 1200 and if they are off it is 1800 here is my query:



Estimated Total: ([EMI_Courses_ALL]![Students Scheduled]+[EMI_Courses_ALL]![Instructors]-[EMI_Courses_ALL]![Off Campus Beds]*1200)+([EMI_Courses_ALL]![Off Campus Beds]*1800)
what am I doing wrong...


v/r

novice

[June7]

Sounds like you need an IIf

What data is in [Off Campus Beds]?

Why do you subtract * 1200 then add * 1800?

[sdc1234]

the data in Off Campus beds are numbers 1,3,5,12, the reason for the subtraction is the fact that they are not off campus beds and the *1800 is when they are

[sdc1234]

I am basically trying to * 1800 by the cells that have off campus beds on them, and the rest by 1200 if there is no data in the field

[sdc1234]

I got it, had the parathesis in the wrong place, thanks

[sdc1234]

Quick ?, I have some of my off campus beds as blanks, I know I need to write an iff, then statement to make the data from my total cost come over, can you provide me with an example and let me know weather or not it should go before the rest of the script?

v/r
novice

[sdc1234]

IIf(IsEmpty([test]),[Estimated Total],([EMI_Courses_ALL]![Students Scheduled]+[EMI_Courses_ALL]![Instructors]-[EMI_Courses_ALL]![Off Campus Beds])*1200+[EMI_Courses_ALL]![Off Campus Beds]*1800)

is there something wrong with my iff statement, I want the values of the estimated cost to come over to all the blanks

[June7]

Are the 'blanks' empty string or Null? Makes a difference. What is [test]?

Arithmetic with Null results in Null. Arithmetic with empty string will error.

I still don't understand the expression.

[sdc1234]

I do have blank cells, here is what I am working with now, it somewhat works although it is changing the value with the off campus beds
test: IIf("*",[Estimated Total],([EMI_Courses_ALL]![Students Scheduled]+[EMI_Courses_ALL]![Instructors]-[EMI_Courses_ALL]![Off Campus Beds])*1200+[EMI_Courses_ALL]![Off Campus Beds]*1800)
I am just trying to bring the values in estimated total over to the empty cells

[June7]

None of that makes sense to me.

Maybe you better provide db.

Identify the form involved.

[sdc1234]

Expr1: IIf([EMI_Courses_ALL]![Location]="CANCELLED" Or [EMI_Courses_ALL]![Location]="MOVED",IIf([EMI_Courses_ALL]![Off Campus Beds]="",IIf([EMI_Courses_ALL]![Type]="E",1200,1800)*([EMI_Courses_ALL]![Students Scheduled]+[EMI_Courses_ALL]![Instructors]),([EMI_Courses_ALL]![Students Scheduled]+[EMI_Courses_ALL]![Instructors]-[EMI_Courses_ALL]![Off Campus Beds])*1200+[EMI_Courses_ALL]![Off Campus Beds]*1800),0)

this is the script I have written, I am just trying to make all the values of the CANCELLED or MOVED classes to =0 but when I run the query, everything changes to 0, is there something in this script I am leaving out?

[June7]

Build the expression in stages.

Start with:

IIf(Location="CANCELLED" Or Location="MOVED", 0, 1)

Now in place of 1 do the nested IIfs. Build them in stages as well.

Off Campus Beds will have empty string in field? More likely field without data will be Null. I don't allow empty string in tables just so I can be certain of Null.

[sdc1234]

thanks it worked much appreciated