Replace values function not working

Question

We have this Access database in which we open an FTP file browser form to process Excel data files.

So we import the excel file and then check the data for quality.  During one of the QC steps, it highlights any non-conforming values and allows us to select valid values that we can choose from.

One of the columns of data contains a monthly range (e.g. 1-2 months, 3-9 months, etc.).  When we attempt to replace the non-confirming monthly ranges with valid ranges, it does not work.  It fails to replace the non-confirming values.

I've tried re-formatting the cells in the excel file to see if that fixes the problem.  It is normally set to General format, and I tried changing it to Text format, but it didn't work.  Needless to say, it's obnoxious having to change each value manually every time this happens.

Does anyone have an idea as to what could be causing this problem and any possible (easy) solutions?

Answer 1

I'm perplexed. It could be Excel, it could be your Replace query (which we cannot see because you haven't posted it), it could be something else - give what's in the thread, I fear I cannot suggest anything else. Good luck.

Answer 2

Unfortunately, I'm not the one who designed it so I can't say what the update expression is.  However, I feel like the problem is stemming from the excel file that it's pulling the data from.

When I open the excel file, I click into one of the cells, then when I click out of it again, it creates a blank line just beneath it, almost like it's either wrapping text or there is some sort of blank value that is creating a conflict.

I tried wiping out the column to get rid of any hidden data and re-typed it into the excel file, but it's still happening for some reason.

The strange part is that when I'm in access and doing the QC step, if I just do a find and replace of the word "months" to the word "month", then refresh the counts, then try to replace the non-confirming values (1-3 month) it works.

Answer 3

HannoverFist probably has the solution, but it would help if you could post the actual update expression that you're using, and a sample of what would be a nonconforming and a conforming range.

Answer 4

Hey MONEA -

Are you saying that the REPLACE function in Access is not working as expected in a query that you're performing? What are you using in the Replace function?

I had a similar issue where some data wasn't gettting changed with the Replace function. My problem was that there are two different dashes - actually ones a dash (Chr 43) and the others a soft hyphen (chr 173). The was occurring when users would paste something from WORD that had a dash that WORD converted to the other character.

You may need to do seperate Replaces with each of the dashes.