Access Replace Function

[prabhus]

In My table, i have a field with values 0 and date. i am using replace function to change all 0 to No using the below replace function. But while i do this, the 0 value in date also converting to No. Like the following 25/5/2015 changing to 25/5/2No15

But i want the data 0 to become No

Replace([Field1], "0", "No")

Field1
0
25/5/2015


Please suggest.

 

[Minty]

You can't have a date field with a value of 0, so you must be storing this as text which is going to get very messy.
I would change your field to store the date as a date and store either a default date 1/1/1900 or no date (null) when you are trying to say No date present.

 

[smbrr]

I don't think you can use replace like that. I would use an IIF function instead:

iif([Field1]=0, "No", [Field1])