IIF statement help

[KenshiroUK]

Hi all I need some help mainly with an IFF statement based on shipping_method

Royal Mail Tracked (2 to 3 working day delivery service for orders up to 2pm) will be TRD

Royal Mail 2nd Class Recorded (Ships normally within 3 to 5 days) will be SC

Royal Mail 1st Class Recorded (Ships normally within 3 to 5 days) will be FC

can somebody help me please?

 

[pr2-eugin]

Hello KenshiroUK, have you tried some IIF statement and stuck somewhere? If so could you provide what you have done so far?

Or if you need to know how to write the IIF could you give another shot at explaining your question, like field names involved, sample data and sample output.. the above description is quiet unclear..

 

[KenshiroUK]

Okay at the moment we have to download orders and we which to give the postal services a code based on shipping_method column. For example Royal Mail Tracked will be TRD, First Class will be FC and Second Class will be SC.

So the IIF staement will go if Royal Mail Tracked it will be TRD, or First Class will be FC and Second Class SC.

 

[pr2-eugin]

Are you saying you will receive statements like "Royal Mail Tracked", "First Class", "Second Class" into the field name shipping_method and you want a Query that will display the appropriate code as TRD, FC or SC?

 

[KenshiroUK]

yes that's what I'm saying

 

[pr2-eugin]

Well you can do IIF, but I think the more easy way would be to create a CODE Table that will have all codes listed.. as..

ID    codeDesc            codeTxt
1    First Class           FC
2    Second Class          SC
3    Royal Mail Tracked    TRD

So when you need the Query all you have to do is simply join them.. Look at the attached sample (sampleQry)

However you can also use IIF.. like..

IIF(shipping_method = 'Royal Mail Tracked','TRD',IIF(shipping_method = 'Second Class', 'SC', 'FC'))

Using a table is just an alternative option.. By having a table you will minimize the chance of logical error that would occur because of improperly nesting IIF's.. But by just updating the description with a code inside the table is far better and in my opinion is far more neat.. But, it is up to you which method you use..