Access Replace function erroring if field is empty

Question

I have a query that contains the following:

Field1: Replace([Field1],"ü",", ")

This works great if there is something in Field1. The data in Field1 usually looks like 1ü2, 0ü0, 1ü1, etc. However, if a record has nothing in Field1 I get the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

If I change the field to Field1: Field1 the query runs with no errors. Is there a way to deal with the possibility of Field1 being blank?

Thanks!

E Mett · Accepted Answer · 2013-08-19 14:01:00Z

4

You need to deal with the possibility of Field1 containing a null value.

The first parameter of the Replace function cannot contain a null value, so you use the nz function convert all null values to something else, in this case it will be ""

Field1: Replace(nz([Field1],""),"ü",", ")

answered Aug 19, 2013 at 14:01

E Mett

2,3223 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nathan Pitman Over a year ago

Thanks so much for this, my Access DB XML export was falling over whenever it hit a row with a null value - this fixed it for me! :)

Collectives™ on Stack Overflow

Access Replace function erroring if field is empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related