Help with embedded if statement

[#1]

Hey,

Can anyone help me with this? for some reason it won't pull in the 'Billable', and I am thinking it is something to do with the dash. The Bus Dev and the Non- Bus Dev are working fine.

if(left(AccountNumber,7)= '-', 'Billable',if(left(AccountNumber,2) = '56', 'Business Development'), 'Non-Business Development')) as [Expense Type]

[m_woolf]

left(AccountNumber,7)  will return the first 7 chartacters at the left of the AccountNumber field.

The first 7 characters can't be equal to "-"

[Anonymous]

is the dash in the field or does it represent a null field

if it represents a null try

if(len(left(AccountNumber,7))>0, 'Billable',if(left(AccountNumber,2) = '56', 'Business Development', 'Non-Business Development')) as [Expense Type]

if the dash is the correct character then you have an extra close bracket

if(left(AccountNumber,7)= '-', 'Billable',if(left(AccountNumber,2) = '56', 'Business Development', 'Non-Business Development')) as [Expense Type]

hope this helps

[Anonymous]

Hi,

you should use mid functions and not left.

In your example if(AccountNumber,7,1)='-' ...

Regards.

[#5]

Makes Sense, but I am trying to pull the first 6 characters as 'Billable' and the account number is 012345-0444.

[MarcoWedel]

If(Mid(AccountNumber,7,1)='-' ...