Basic if statement issue - quick help please!

[#1]

Quick easy question, having difficulty with an if statement, at the moment it looks like this:

if([Invoice PO Number]>0,'PO',

if(isnull([Invoice PO Number])<0 AND [Document Type] <> ('VA','VD','VF'),'Non-PO',

if(isnull([Invoice PO Number])<0 AND [Document Type] =('VA','VD','VF'),'Awaiting-PO'

)))

as [Voucher Type],

The problem is it doesn't work, do I need to change the syntax for VA or VD or VF? How should this be written? When I have used it as this:

if([Invoice PO Number]>0,'PO',

if(isnull([Invoice PO Number])<0 AND [Document Type] <> 'VA'or'VD'or'VF','Non-PO',

if(isnull([Invoice PO Number])<0 AND [Document Type] =('VA'or'VD'or'VF','Awaiting-PO'

)))

as [Voucher Type],

I still have a problem, in so much as the 'Awaiting-PO' field doesn't seem to appear in the list, instead I have a null value.

Can anyone correct this bit of code for me??!!

Help!

Thanks!

[#2]

you need to repeat the subject of the conditioning e.g.

AND ([Document Type] = 'VA' OR [Document Type] = 'VD' OR [Document Type] = 'VF')

or better still use mixmatch:

AND (mixmatch([Document Type], 'VA', 'VD', 'VF') > 0)

Regards,

Gordon

[#3]

Hi,

Expression should be as below

if([Invoice PO Number]>0,'PO',

if(isnull([Invoice PO Number])<0 AND ([Document Type] <> 'VA' or [Document Type] <>'VD' or [Document Type] <>'VF'),'Non-PO',

if(isnull([Invoice PO Number])<0 AND ([Document Type] ='VA'or [Document Type] ='VD'or [Document Type] ='VF'),'Awaiting-PO'

)))

as [Voucher Type],

- Peterson

[tseebach]

Hi,

You don't need <0 isnull returns true or false.

I've had some issues where it did return as expected, so I used len([Invoice PO Number])>1 to test for a valid value instead.

[johnw · Accepted solution]

Putting it all together, I think you get this:

,if(Invoice PO Number]>0,'PO'
,if(len([Invoice PO Number])=0
,if(match([Document Type],'VA','VD','VF'),'Awaiting-PO','Non-PO'))) as [Voucher Type]

Now this expression CAN assign a null Voucher Type if the Invoice PO Number is literally 0 instead of null. Is that what you want? Or do you want a 0 PO to be treated like a null PO? In which case, there's no need to check for null, and you'd have this:

,if(Invoice PO Number]>0,'PO'
,if(match([Document Type],'VA','VD','VF'),'Awaiting-PO','Non-PO')) as [Voucher Type]

[#6]

Thanks very much all - really helped to sort out the issue very quickly! Much appreciated!

[#7]

Try this

if([Invoice PO Number]>0,'PO',

if(isnull([Invoice PO Number]) AND ([Document Type] <> 'VA' or [Document Type] <> 'VD'or [Document Type] <> 'VF'),'Non-PO',

if(isnull([Invoice PO Number]) AND ([Document Type] ='VA'or[Document Type] ='VD'or[Document Type] ='VF'),'Awaiting-PO')))as [Voucher Type]

Zaman