*pic* Conditional Formatting to change Other Field Bold

[KrenzyRyan]

Please see attached screenshot.



In the case below, depending on what is in the "WC" field, I would like for it to BOLD those same characters in the "Remaining Operations" field.

Any help would be appreciated guys, thank you!

[rpeare]

you can't format part of a string, however, if that string is ALWAYS going to appear in the same part of your string (positions 3 through 5) then you can rig something up to make it look the way you want.

If the string can appear anywhere in that long field then it's a bit uglier but it can still be done, however, it the spacing will not look right.

The basic gist is that you'd have to break up your string into substrings using the left(string, length), mid (string, starting position, length) and right(string, length) functions.

[KrenzyRyan]

Looks like this one will be way over my head...

[rpeare]

it's not that hard, you just have to say know whether or not that same string will appear in the same position every time or not, even if it doesn't there are solutions but I'd sort of have to know if the code you've shown is the ONLY code that needs a partial bold or if there are other codes and whether or not the string can appear anywhere in the longer string or not.

[KrenzyRyan]

Thank you for your help rpeare,

To answer your questions:

-The same string will not appear in the same position every time. The "remaining" operations field will always be a different length and have multiple combinations of letters/operations.

-The "WC" field can appear anywhere in the longer string "remaining operations"

-The "WC" lettering (in this case SRC), is the only thing that needs to be bold on the "remaining operations" field.

-If "WC" lettering said EDC, EDC would need to be bold on the long string.

Thank you again for your help.

[rpeare]

hrm Would it serve your purpose if the string in your WC field appeared in the remaining operations field had a YES or a checkbox with a check in it appear on your report rather than having to bold the string itself? Is it allowable to re-arrange the remaining operations string so that the text in the WC field appears first followed by everything else?

[KrenzyRyan]

Your second suggestion would be the ultimate solution!

If the text int he WC field appeared first followed by everything else, that would be better than bolding it!!

Is it possible?

Thanks again!!

[rpeare]

Ok, I made a table called tblTest

In it I had two fields

WC
RemainingOperations

Note there are no spaces in the field names.

I built the query below, I've left in a lot of fields in there just to try and make it easier for you to see how I built the final string.

If you build this string in your query you can display it instead of your remainingoperations field on your report

SELECT tblTest.WC, tblTest.RemainingOperations, InStr([remainingoperations],[wc]) AS StartString, IIf(InStr([remainingoperations],[wc])=0,0,IIf(InStr(InStr([remainingoperations],[wc]),[remainingoperations],"-")=0,Len([remainingoperations]),InStr(InStr([remainingoperations],[wc]),[remainingoperations],"-"))) AS EndString, Mid([remainingoperations],[startstring],[endstring]-[startstring]+1) & IIf(Right(Mid([remainingoperations],[startstring],[endstring]-[startstring]+1),1)<>"-","-",Null) AS StartPart, IIf(Len([remainingoperations])=[endstring],Left(IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1))),Len(IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1))))-1),IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1)))) AS LeftPart, IIf([endstring]<>Len([remainingoperations]),Right([remainingoperations],Len([remainingoperations])-[endstring]),Null) AS RightPart, IIf([startstring]<=1,[remainingoperations],Mid([remainingoperations],[startstring],[endstring]-[startstring]+1) & IIf(Right(Mid([remainingoperations],[startstring],[endstring]-[startstring]+1),1)<>"-","-",Null) & IIf(Len([remainingoperations])=[endstring],Left(IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1))),Len(IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1))))-1),IIf([startstring]<=1,Null,IIf(Len(Left([remainingoperations],[startstring]-1))=[endstring],"XXX",Left([remainingoperations],[startstring]-1)))) & IIf([endstring]<>Len([remainingoperations]),Right([remainingoperations],Len([remainingoperations])-[endstring]),Null)) AS ROConv
FROM tblTest

[KrenzyRyan]

You sir, are the best!

Your query works great, the "RightPart" is just about exactly what I need, except it doesn't pull the original "WC" to the front. From what it looks like, you were trying to attempt this with the ROConv Field?

I see it will put the WC in front, but the ROConv is the entire "RemainingOperations" field.

Did you have to write all of that code by hand?

[rpeare]

I'm not following you.

In the testing I ran if the string that's in WC is also in RemainingOperations that string is put at the front, from there it figures out what was to the right of the string and adds that second, then if figures out what was to the left of the string and adds that so the net result, again in my testing, is that you have the entire original string, it's just rearranged so that the WC string is at the start of the field

[KrenzyRyan]

Oh, okay.

And it does just that, you are right!

Would there be a way to have it re-arrange to add the WC string at the start of the "Right Part" field? This would make everything perfect!

Everyone from here at work thanks you good sir. Everyone is very much appreciative of your efforts, we can't thank you enough!

[rpeare]

Why do you need to add the string to the right part when the full string gives you what you want? You could do it just by changing the formula to be

Right Part: [WC] & "-" & <existing formula for right part>

Stumped as to why you'd only want a partial string when the full string works.

[KrenzyRyan]

Rpeare, thanks again, What more do I need to add to the code so the #Error does not appear if WC is null?

[rpeare]

iif(isnull([wc]), null, [WC] & "-") & <existing formula for right part>

[KrenzyRyan]

Hmm, tried it, still giving me the #Error