Multi Criteria Case Statement

[Ron Cates]

Hello All,

I seem to be having a little trouble with the syntax for a case statement. I am looking to set a field as follows;

Tickets::status_paid=

If Tickets::status="Billing"; "Unpaid"

If Tickets::status="Paid Billing" or Tickets::status="Collected"; "Paid"

If Tickets::status="Unavailable"; "?"

Else Tickets::status

I am confused as to where all the brakets and parens go so I keep getting an error when I try to set the calculation. Any help would be greatly appreciated.

[LaRetta]

When you have multiple tests, it works better to use Case() than attempting to use If() particularly since you must nest If()s because (natively) they only accept two results).

Try:

Case (

Status = "Billing" ; "Unpaid" ;

Status = "Paid Billing" or Status = "Collected" ; "Paid" ;

Status = "Unavailable" ; "?" ;

Status

)

LaRetta

[Ron Cates]

It seems to come up "Unpaid" on all. It looks like it needs some "{}" brackets but I can't figure it out from the example FM gives.

Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

Edited May 29, 2009 by Guest

[LaRetta]

Well, this is what it does for me (attached).

test_tickets.zip

[LaRetta]

Is the Tickets::Status field from a related table?

[Ron Cates]

No it's in the same table. I think I have a case of Friday burn out. I can't see why yours is working and mine isn't. I think I'll call it a day and look at it Monday.

Thank you so much for your help.

Edited May 29, 2009 by Guest

[Ron Cates]

Doh!! OK, Definitely a Friday thing. The field I was using to test with was from the wrong table. I'm going home. Lol

Thanks again