Multiple Functions

[pwoodj]

I have a query that contains a field with 4 codes that I need to replace with values.

Example:
Replace([CE020]![LNCH_FLG],"0","None") Replace([CE020]![LNCH_FLG],"1","Free") Replace([CE020]![LNCH_FLG],"2","Reduced")
Replace([CE020]![LNCH_FLG],"3","State Free")
Replace([CE020]![LNCH_FLG],"4"," Paid")


I get an invalid syntax error, I know if I was to have one Replace function it will works fine, but I’ll still have 3 code to convert.

Any Ideas?

Thanks
--pete

 

[KenHigg]

Is 0,1,2,3 & 4 the only value in LNCH_FLG or are these part of a bigger string?

 

[pwoodj]

Yes, just the 5 numbers.

 

[KenHigg]

Then I would simply make these constants and call them with a function or put them in a seperate table and link 'em when needed...

 

[pwoodj]

Lookup table... Thanks for the help

 

[pwoodj]

It seems to slow it down a little, but it works, thanks again..

 

[KenHigg]

You may want to index something and see if that helps...

 

[raskew]

Hi-

If you're going from "0" to "None", etc., the following update query should get it:

UPDATE CEO20 SET CEO20.lnch_flg = Choose(Val([lnch_flg])+1,"None","Free","Reduced","State Free","Paid");

HTH - Bob