Avatar of TAI-
TAI-
Flag for Turkey 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
Crystal Reports

Avatar of undefined
Last Comment
TAI-

8/22/2022 - Mon
SOLUTION
Rajesh Dalmia

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
Sign up - Free for 7 days
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
Rajesh Dalmia

In the above formula u have to mention all the field names one by one and have to use the + sign in them


rdonline1
SOLUTION
peter57r

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
Sign up - Free for 7 days
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
TAI-

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?
Rajesh Dalmia

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

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

Which version of CR are you using?

Pete
TAI-

ASKER
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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
frodoman

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

ASKER
Thank you bdreed35,

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

bdreed35

TAI-

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

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

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

ty for instr formula...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.