Can't find blank records after replace function

[jpaokx]

Hi,

Probably, this is something silly, but I need to understand how to solve this.

I have a title field for which I wanted to replace all the "N/A" to null. I used the replace function as =replace(title,"N/A",""). This made those records as null (as expected). However, if I run a selection query to find all records who have the title field as null, then it doesn't work. It will find all those records that were null in the first place.
I think that Access thinks that, when I apply the replace function, the field is "populated" with null value(?).

What do you think? How can I pick all those null records?

Thanks!

 

[plog]

This made those records as null (as expected)

Incorrect. You made those values an empty string. Technically (and that's the realm we are in) there is a difference--feel free to google the difference and be bored by a thousand blogs trying to differentiate the two.

From a practical view, that means to find those values you would need to put "" in the criteria section and not Is Null.

If you would truly like to make those values Null, you wouldn't use the Replace function, you would use an UPDATE query and set all the values you identify to Null (put that in your update to field of the query).

 

[jpaokx]

Cool...I used this one and it worked!

UPDATE test SET test.title=NULL
WHERE (((test.title)="N/A"));

Thanks!