calculated field criteria

[dixxy]

Hello,

I am having trouble with a calculated field. This is what I have as source for the field:
="Total " & " = " & " (" & Count([Type]) & " " & IIf(Count([Type])=1,"detail record","Jobs") & ")"

What I would like it to do is add to it a little and have criteria in there. Were [type] could be one of 2 things:

either 'Extra' or 'Back Order'

I have tried several different senarios, but no luck :-(

Can this be done?

Thanks,

Sylvain

 

[snyperx3]

Are 'Extra' and 'Back Order' other fields in the table?

And Yes it can be done. =]

-Pete

 

[dixxy]

No, they are the data that make up [Type].

So in other words there is 2 values for [Type] Extra, Back Order.



Thanks,

Sylvain

 

[snyperx3]

so you want to only use the line:
="Total " & " = " & " (" & Count([Type]) & " " & IIf(Count([Type])=1,"detail record","Jobs") & ")"

when type is extra or back order?

-Pete

 

[dixxy]

yes

Thanks,

Sylvain

 

[snyperx3]

This will be kinda long =]

=IIf([Type]="Extra","Total " & " = " & " (" & Count([Type]) & " " & IIf(Count([Type])=1,"detail record","Jobs") & ")",IIf([Type]="Back Order","Total " & " = " & " (" & Count([Type]) & " " & IIf(Count([Type])=1,"detail record","Jobs") & ")",""))

-Pete

 

[dixxy]

ok i misunderstud your question....i will put 2 seperate formulas, one to count 'extras' and on to count 'back order'

sorry about that..

Thanks,

Sylvain

 

[snyperx3]

rgr

ill put a new line where you insert your code...just remember to remove the new line for each =].

=IIf([Type]="Extra",
,IIf([Type]="Back Order",
,""))

this is pretty much - if type = extra then...else if type = backorder then...else blank

Hope this helps you.

-Pete

 

[dixxy]

this is what i have and it does not seem to work:

=IIf([Type]="back order","Total " & " = " & " (" & Count([Type]) & " " & IIf(Count([Type])=1,"detail record","Back Order") & ")")

and the same thing for the extra in a differetn text field

Thanks,

Sylvain

 

[snyperx3]

what does it show in the field?

-Pete

 

[dixxy]

it gives the same results for both of them, although they should be different....and i have phisicaly checked the data and it is different.

Thanks,

Sylvain

 

[snyperx3]

gotcha. in the code its not counting the type based on the value of type. i thought you wanted to display it differently with the same count. i apologize for misunderstanding the problem. my fault =\

try this for 'Extra'
="Total " & " = " & " (" & Count(IIf([Type]="Extra",[Type],0)) & " " & IIf(Count(IIf([Type]="Extra",[Type],0))=1,"detail record","Extra") & ")"

-Pete

 

[PHV]

A starting point:
="Total = (" & Sum(IIf([Type]="back order",1,0)) & " Back Order)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixxy]

PH....that works like a sharm!!!

Thanks for your help Pete....

Thanks,

Sylvain