Decode function

[waynered]

Does anyone know what I need to do in access to replace values in my query results with new values I specify? There is a standard SQL function called "DECODE" but Access does not recognise this.

Any help appreciated
Wayne

 

[Norie]

Wayne

Can you explain further what you want to do?

Are you trying to update a field in the underlying table depending on the current value in the field?

 

[Stubby Holder]

Hi,

To emulate the Decode function in Access you need to use multiple IIfs eg

Decode([YourField],"Val1","AltVal1","Val2","AltVal2","OtherwiseThis")

would be

IIf([YourField]="Val1","AltVal1",IIf([YourField]="Val2","AltVal2","OtherwiseThis"))

You could use a custom function, but if your query needs a large number of replacements, VBA would be much slower than IIfs.

HTH, Stubby

 

[Norie]

Stubby

From what you posted it sounds like DECODE works like the Choose or Switch function.

Is that right?

 

[Stubby Holder]

Hi, Norie

Sort of...Choose & Switch give you "If This Then This", but Decode adds an "Else This", a default value, for unknowns or unspecifiied.

I think Choose requires picking from a list based on the position, so I'm not sure that would work, but Switch would work if a default value isn't needed.

If a default value is needed, & if there are more replacements needed than allowed nested IIfs (seven?), a custom function using select case would probably be the easiest...

Stubby

 

[Norie]

Stubby

Is there is a limit for nested Iifs?

I know in Excel the nested IF limit is 7.

I've only occasionly used Iifs in Access, normally just 2 or 3.

When you say using a custom function would be slower I think it really depends what is being done.

I think the main problem with a custom function is it is a 'custom' function specific to the particular needs.

 

[firemark1817]

Why not use a Decode Table?

If you have a large number of codes to deal with why not put them in a separate Table and just join the primary data table to it to get you decoded values?