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

Crystal Reports

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionLog in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

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

Pete, I'm using CR XI.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

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

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

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...

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

rdonline1