IIF STATEMENT

[DIY]

Hi All,

Can anyone help please?

I have a Field called (Staff Trade) ~ showing the Trade Type of a staff and
Staff Code ~ showing the Staff Id

Staff Trade can be a letter A, B, C, D, etc.

I am trying to create a statement that says,
If the staff trade = “A†and Staff Code starts with a “7†then make the
value “WNâ€, else leave the original value, i.e., B,C,D etc.

This is what I have so far which works for the first part of the argument,
i.e., it changes anything starting with “7†to “WCâ€.
=IIf([STAFF TRADE]="A" And [Staff Code] Like "7*","WN", …)

I am struggling with the second part of the statement to leave the original
value, B, C, D, etc.

Kind regards,

 

[Willem]

Create a function with a select case statement and databind your
control to that function.

 

[DIY]

thanks for your response Willem.
How do I go about it

 

[Willem]

Not entirely sure what you are trying to achieve. Read it again and
seems like you don't need a select case statement

this might be all you need:
=IIf([STAFF TRADE]="A" And Left([STAFF CODE];1)="7";"WN";[STAFF TRADE])

in case STAFF CODE is numeric:
=IIf([STAFF TRADE]="A" And Left(cstr([STAFF CODE]);1)="7";"WN";[STAFF
TRADE])

if you need more, better create a function:

Function setStaff (stafftrade, staffcode) as string
' do your stuff here

end function

Control Source: setStaff([STAFF TRADE];[STAFF CODE])

 

[DIY]

Thanks Willem,
It worked a great treat.