Jump to content

A variation on the substitute function


This topic is 7562 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I need to do the following:

If a word in present in a field (for example, the word "blue") then it is replaced by the word, "green". I use the Substitute function for this. But if the word "blue" is not present, then I want to return the word "yellow". How can I do this?

Thanks so much,

Jason

Link to comment
Share on other sites

Just in case you didn't know, the Substitute function can also be nested like this:

Substitute(

Substitute(

Substitute(

field,

"blue", "green" ),

"red", "yellow" ),

"pink", "purple" )

Link to comment
Share on other sites

You mean like Lower( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( c.t.description|major, "

Link to comment
Share on other sites

What I need is:

If blue, then green

If not blue, then yellow

I may be wrong, but I don't think the suggested nested Substitute function will deliver this result:

Substitute(

Substitute(

Substitute(

field,

"blue", "green" ),

"red", "yellow" ),

"pink", "purple" )

My application is for writing eye drug prescriptions. Most eye drug are drops. So if you select Drug A, the prescription reads, " Drop A: 1 drop 4 times per day". But a few eye drugs are ointments, and read, "Drug B: 1 strip 4 times per day". The drug value list contains drops by drug name alone, but ointments are listed as Drug name + "ointment". So, if the value list choice contains the word "ointment", then the the word "drop" becomes "strip". If the value list choice does not contain the word "ointment", then the calculation returns "drop".

Thus,

If blue, then green

If not blue, then yellow

There are probably better ways to design this, were I to start over, such as having a field with drop or ointment as a choice, but on the other hand, this is just one more thing to click and slow the user down. So, I'd like to solve this with a calculation or script. Thanks for your advice!

Jason

Link to comment
Share on other sites

or

Substitute(Prescription, "Drop",

Case(Drug="ointment","STRIP","DROP"))

So if you select Drug A, the prescription reads, " Drop A: 1 drop 4 times per day". But a few eye drugs are ointments, and read, "Drug B: 1 strip 4 times per day".

or rather use something like :

c_Prescription = DrugSelected & " "& NumberOfDrop & " "& Case(DrugSelected = "Ointment", "Strip", "Drop") & " " & Interval & " " & "times per day"

Link to comment
Share on other sites

Queue,

I've looked your suggestion, but still can't get it to work. I've attached a file to illustrate my problem. If I select "Antibiotic" from the Drug field, it works fine (returning, "Antibiotic: 1 drop four times per day")

But if I select "Antibiotic ointment", I don't get my desired result: "Antibiotic Ointment: 1 strip four times per day".

Thanks for your help,

Jason

Link to comment
Share on other sites

Change your substitution field to: Case( PatternCount(Drug, "Ointment"), "strip", "drop" )

The way you are doing it, you're really not substituting and could include this calculation in your Prescription field.

Link to comment
Share on other sites

Jason, you are right, my example didn't address your specific question. I only offered for the sake of completeness -- and because sometimes, people don't ask exactly what they mean. Glad to see your problem resolved.

-Q-, here in the forums, I generally use the Quick Reply box at the bottom of the page. So, although my post may have said "in response to -Q-," it was really more of a general comment. I was not intending to imply that you didn't know how to use a common function. cool.gif

Link to comment
Share on other sites

This topic is 7562 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.