Search through a table

[rpeare]

here's what I would do, create a small table for each one of the variables you want to 'translate' and what you want the 'final string to be, then build a query around linking the tables rather than trying to do this all with code, the processing time will be ridiculous in it's current format.

[Thompyt]

Can you provide an example for me to go off of? I've been looking through the internet for it. No luck so far.

thanks

[rpeare]

BN_ID	BN	BNConv	BNZVal
1	BDE	BDEs	1
2	INF BN 1	INFBN	3
3	BEB	BEB	7
4	BSB	BSB	9
5	DIV	DIV	0

You have a field named 'bn' in your table NBOI your 'translation' table would be something like the above

CO_ID	CO	COConv
1	HHC	HHC
2	A	A
3	B	B
4	C	C
5	D	D

Similarly you have a CO field in your NBOI table, this would be your translator, everything other than these values would be considered 'null'

Then when you have all your 'translator' tables set up use concantenated fields to build your 'final' string.

[Thompyt]

I see what you are doing there, basically putting the select case portion of the code into a table.

[rpeare]

exactly, it will cut down on your processing time and allow you to do one edit per record.

[Thompyt]

rpeare,
The difficulties lie in that the rows which don't equal anything in the NewNetID table, it doesn't show. I gather I need to develop another query just for those?

[rpeare]

if you want a value for a 'null' you can say something like

iif(isnull([fieldname]), "My Null Value Here", [Lookup Field Name])