Erin Leale
asked on
Replace Data
Hi:
I have a field {itemcode} this field contains the data almost in a memo format with possibly a return. I need to replace the line in the field that starts with Contract and Off Contract for 30 characters with " " - can this be done?
Example of what field looks like on the form - (it is one field with Can Grow turned on and I need the Can Grow turned on for the items that are not contract items.)
Ultra Low Clear
Contract: 3000 GAL @ $2.2002
Off Contract: 4000 GAL @ $2.2800
I need to have the field for this item show on the Form as:
Ultra Low Clear
Any help is appreciated.
Thank you.
I have a field {itemcode} this field contains the data almost in a memo format with possibly a return. I need to replace the line in the field that starts with Contract and Off Contract for 30 characters with " " - can this be done?
Example of what field looks like on the form - (it is one field with Can Grow turned on and I need the Can Grow turned on for the items that are not contract items.)
Ultra Low Clear
Contract: 3000 GAL @ $2.2002
Off Contract: 4000 GAL @ $2.2800
I need to have the field for this item show on the Form as:
Ultra Low Clear
Any help is appreciated.
Thank you.
ASKER
Yes. It might have ##### for the gallons but that would be rare.
Thanks.
Thanks.
I assume the field has data like
Ultra Low Clear
Contract: 3000 GAL @ $2.2002
Some text you want to keep
Off Contract: 4000 GAL @ $2.2800
Other text you want to keep
You want to see
Ultra Low Clear
Some text you want to keep
Other text you want to keep
mlmcc
Ultra Low Clear
Contract: 3000 GAL @ $2.2002
Some text you want to keep
Off Contract: 4000 GAL @ $2.2800
Other text you want to keep
You want to see
Ultra Low Clear
Some text you want to keep
Other text you want to keep
mlmcc
ASKER
On those where the 2nd and 3rd line start with Contract: and Off Contract: I want to eliminate those lines only. They will always show Contract then right below Off Contract if the customer exceeded the contract gallons.
Thanks.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FWIW, you could also do this using functions like Mid, but mlmcc's approach seems simpler.
There's a typo on the For line. It should be:
For Index := 1 to UBound(strLines) do
Also, you could simplify it a bit by combining the 2 tests:
If InStr(strLines[Index], 'Contract:') = 1 or
InStr(strLines[Index], 'Off Contract:') = 1 then
strLines[Index] := '';
FWIW, I think I'd use Left instead of InStr:
If Left (strLines[Index], 9) = 'Contract:' or
Left (strLines[Index], 13) = 'Off Contract:' then
strLines[Index] := '';
The result's the same either way, but Left would theoretically be more efficient, since InStr would search the entire line for the target string, but you're only interested in the first characters.
However, having said all of that, I have also realized that the formula probably doesn't quite work the way you'd want. You end up with blank lines where the "Contract" and "Off Contract" lines were. I don't know if there's an easy fix for that. My first thought would be to loop through the lines after one of those lines, and move them all up 1 spot in the array. But maybe there's a simpler solution.
James
There's a typo on the For line. It should be:
For Index := 1 to UBound(strLines) do
Also, you could simplify it a bit by combining the 2 tests:
If InStr(strLines[Index], 'Contract:') = 1 or
InStr(strLines[Index], 'Off Contract:') = 1 then
strLines[Index] := '';
FWIW, I think I'd use Left instead of InStr:
If Left (strLines[Index], 9) = 'Contract:' or
Left (strLines[Index], 13) = 'Off Contract:' then
strLines[Index] := '';
The result's the same either way, but Left would theoretically be more efficient, since InStr would search the entire line for the target string, but you're only interested in the first characters.
However, having said all of that, I have also realized that the formula probably doesn't quite work the way you'd want. You end up with blank lines where the "Contract" and "Off Contract" lines were. I don't know if there's an easy fix for that. My first thought would be to loop through the lines after one of those lines, and move them all up 1 spot in the array. But maybe there's a simpler solution.
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! That worked! I tested it with extended description information other than the Contract and Off Contract and it seems to work great. Still testing but thank you - thank you!
You're welcome. Glad I could help.
James
James
IE
CONTRACT: #### GAL @ $#.####
OFF CONTRACT: #### GAL @ $#.####
mlmcc