Calculated Field Replaces Character, Exclude Errors From Query Results

[bs0d]

I have Make Table query that evaluates a field, replaces an undesired character ("*") with another ("_"). The replace function within the calculated field works correctly, however errors appear in the results. This creates problems down the line.

Is it possible to somehow exclude the errors from appearing in the query results? Here's a specific example:

Calc_Field: Replace([Target_Field], "*", "_")

I've tried adding this to the Criteria, without success:

Not IsError(Replace([Target_Field], "*", "_"))

The errors I see are #Error , and #Name ... The original Target_Field values are all over the place, which is why I'm attempting to clean it up with the Make Table query. I'm ok with simply not including any records that resulted in an error or were blank for example.

Thanks!

 

[Joe4]

Can you provide some example values that lead to errors?

 

[bs0d]

Can you provide some example values that lead to errors?

So it's actually looking like the problem records are NULL values. I'm finding that if I add the source field to the query (by itself) with a criteria "Is Not Null" that it appears to be filtering the list correctly without errors in the calculated field (that replaces * with _).

 

[Joe4]

That is what I thought might be going on. Yep, filter those out and you should be good.

 

[bs0d]

That is what I thought might be going on. Yep, filter those out and you should be good.

How might I limit to a distinct list, with the distinct criteria enforced only the calculated field? I have another field in my query, "Area". Testing "SELECT Distinct... ", this certainly cuts down on the list. But it seems to include duplicate records of calculated_field if more than one unique "Area" exists per calculated_field.

 

[Joe4]

Using DISTINCT, it is going to include all of the fields in your SELECT clause to determine what is a duplicate.
The field that you are using for your Criteria, uncheck the "Show" box under it, and see if that resolves your issue.