Replace Multiple Values from Column.

[adam]

I have rows which contain only 0 and rows which contain 1 in column "Location" of my access table.

I want to replace 0 as No and 1 as Yes when the values get copied to range D5.

I have tried the following. But it's not working.

Code: Select all

  Range("D5").Value = rst!REPLACE(Location, 1, 'Yes'), REPLACE(Location, 0, 'No')  

How can I do this. Any help would be appreciated.

[HansV]

rst!Location is a value. You cannot use rst!REPLACE(...). Use Replace(rst!Location, ...) instead:

Code: Select all

  Range("D5").Value = Replace(Replace(rst!Location, 1, 'Yes'), 0, 'No'))

[adam]

I'm getting an expression error when I copy and paste your line of code. What may be the reason?

[HansV]

I have no idea, I'd have to see the workbook and database.

[Doc.AElstein]

Sorry if this is a dopey reply, as I know nothing at all about Access, but, Is the syntax in Access different to Excel VBA?
In Excel VBA the code line has typos , - using ' instead of " and an extra bracket at the end
In Excel VBA it needs to be like
Range("D5").Value = Replace(Replace(rst!Location, 1, "Yes"), 0, "No")

[adam]

The following works for me.

Code: Select all

Range("D5").Value = Replace(Replace(rst![Gender], "M", "Male"), "F", "Female")

However, I still cannot make

Code: Select all

Range("D5").Value = Replace(Replace(rst!Location, 1, "Yes"), 0, "No")

work.

[HansV]

Perhaps

Range("D5").Value = IIf(rst!Location = 0, "No", "Yes")

[adam]

Thankyou very much Hans. It worked fine.