Replace function with multiple joined tables

[MGAllen]

Hello experts,
I was able to utilize the replace feature with one table but now am having difficulty coding it to work with multiple joined tables.

This works with no joined tables:
Replace(Replace(Replace([Degrees],"Copy HS Diploma",""),"GED",""),"High School transcript;","")

What I've discovered is, the query I'm using has joined tables and I receive a data type mismatch error and/or too complex error.

(don't laugh at my table name - I inherited it ;( Master Faculty_2010-11 and column is "Degrees"

I tried this with no success:

Replace(Replace(Replace([Master Faculty_2010-11].[Degrees],"Copy HS Diploma",""),"GED",""),"High School transcript;","")

Any advice to correcting this query is greatly appreciated.
Mel

 

[Pat Hartman]

Use an immediate If:
IIf([Degrees] = "Copy HS Diploma", "", IIf([Degrees] = "GED", "", IIf([Degrees] = "High School transcript;", "", [Degrees])))

Are you sure you want to change the values to a ZLS string? I would suggest Null instead. Having text columns with ZLS as well as Nulls just complicates your life and your queries.

 

[MGAllen]

Thank you Pat,
Let me warn you - I'm a beginner/moderate access user . . .

I have found that if I use the code
Replace(Replace(Replace([How Criteria Met?],"Copy HS Diploma",""),"GED",""),"High School transcript;","")
I lose records - about 25 or so. So what you are describing above sounds wonderful to use!

Let me try that and get back with you and thank you so much for your suggestion! Crossing my fingers ;-)
Mel

 

[MGAllen]

I changed the code and placed it in design view under criteria (carefully to How Criteria Met?):

IIf([How Criteria Met?]="Copy HS Diploma","-",IIf([How Criteria Met?]="GED","-",IIf([How Criteria Met?]="High School transcript;","-",[How Criteria Met?])))

It ran and I didn't lose records, however, I still see the wording Copy HS Diploma.

Any other suggestions?
This may be a stupid question, but why is [How Criteria Met?]))) at the end as well?

thanks!
Mel

 

[Pat Hartman]

It will return the actual value rather than "-" for values not in the list. Check your typing for the HS Diploma issue.