Tusas BT

asked on

# How to find specified values among various fields.

Hello,

We need to write a formula which finds specified values among various fields. For example;

We wantto find the value "1A3" (string) in A1, A2, A3, A4, A5,...,A40 fields in a report and if there is one or more "1A3" values in any of these fields, our formula will take the value "S3".

Asking this because we need to search for lots of values like "1A3" in about 40 fields and assign a new value with this formula and it is really hard to do that by using the basic if-then-else structure. So we need 2 find out if there is a practical way.

Waiting for your urgent comments.

With Best Regards.

TAI

We need to write a formula which finds specified values among various fields. For example;

We wantto find the value "1A3" (string) in A1, A2, A3, A4, A5,...,A40 fields in a report and if there is one or more "1A3" values in any of these fields, our formula will take the value "S3".

Asking this because we need to search for lots of values like "1A3" in about 40 fields and assign a new value with this formula and it is really hard to do that by using the basic if-then-else structure. So we need 2 find out if there is a practical way.

Waiting for your urgent comments.

With Best Regards.

TAI

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

rdonline1 and Pete... Thank you so much for your help. Both answers are cool for me. I'll split points 2 both of you, but there's a problem with stringvar solution;

its working great up to 14 fields. but its not working more than 14?

its working great up to 14 fields. but its not working more than 14?

in that case u can try using

If InStr({A1} + {A2}+ {A3}+ {A4}+ ... + {A14},"1A3",1) >= 1 OR InStr({A15 + {A2}+ {A3}+ {A4}+ ... + {A29},"1A3",1) >= 1 OR InStr({A30} + {A2}+ {A3}+ {A4}+ ... + {A44},"1A3",1) >= 1Then

"S3"

Else

"1A3"

like this

rdonline1

If InStr({A1} + {A2}+ {A3}+ {A4}+ ... + {A14},"1A3",1) >= 1 OR InStr({A15 + {A2}+ {A3}+ {A4}+ ... + {A29},"1A3",1) >= 1 OR InStr({A30} + {A2}+ {A3}+ {A4}+ ... + {A44},"1A3",1) >= 1Then

"S3"

Else

"1A3"

like this

rdonline1

ASKER

thank you rdonline, but it would be gr8 if i could make it by using stringvar. because i'll search for lots of values in these fields and i dont want to see

i'd be appreciated if anyone could help about stringvar...

wbr.

i'd be appreciated if anyone could help about stringvar...

wbr.

Which version of CR are you using?

Pete

Pete

ASKER

Pete, I'm using CR XI.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

TAI - There shouldn't be a limit of the number of fields but there is on size. Are these fields very large? - frodoman

ASKER

Thank you bdreed35,

but it didnot work.. gives an error to formula; "the result of formula cannot be an array"...

but it didnot work.. gives an error to formula; "the result of formula cannot be an array"...

TAI-

Can you post the exact formula that you are using and then tell us where the highlighting starts when the error occurs?

Can you post the exact formula that you are using and then tell us where the highlighting starts when the error occurs?

ASKER

bdreed35,

its okay now, made a mistake and corrected it. Thank you.

Frodoman;

3 characters for each fields i dont know whats wrong but i saw some errors about stringvar solution. cause its not working for every record and i dont know why...

Looks like Using arrays solved my problem. Thank you all. I'll give 300 points to bdreed, 100 points to rdnline1 and 100 points to pete.

Thank you.

With best regards.

TAI

its okay now, made a mistake and corrected it. Thank you.

Frodoman;

3 characters for each fields i dont know whats wrong but i saw some errors about stringvar solution. cause its not working for every record and i dont know why...

Looks like Using arrays solved my problem. Thank you all. I'll give 300 points to bdreed, 100 points to rdnline1 and 100 points to pete.

Thank you.

With best regards.

TAI

ASKER

Noticed that all solutions are working correctly for 4 fields maximum; but not for more than 4 fields... it start to give blank values for more than 4 fields and it gives all blank values for more than 14 fields..

How can this happen? Any ideas?

Wbr

ASKER

created a view and joined all fields into one field and then used instr formulas to solve problem...

ty for instr formula...

ty for instr formula...

rdonline1