MS ACCESS replacing wildcard and underscore always stopped working

Question

case: i need to change the barcode if it contains _A, _B, _C into "". for example.

image_a into image , build_b into build

everytime I query

update table set barcode = replace(barcode,"\_*", "")

then it crashes.

an update:

the problem more like if the query contains replace function or instr stopped working. no debug option and simply restarting.

Any help would be appreciated.

*How* does it crash? And do you get a specific error, or anything in the Event Viewer, or etc.? — underscore_d, Nov 17 '17 at 11:11
no specific error, it just simply "access stopped working.." — Anthony, Nov 17 '17 at 11:34
so far my assumption that access can't handle special characters such as _ in replace function. not sure tho. — Anthony, Nov 17 '17 at 11:36
The `Replace` function doesn't take patterns or wildcards, they just do nothing. This is no explanation for the crash, however. — Erik A, Nov 17 '17 at 11:36
i see, i've tried only "_" without wild card in condition and still no luck. I think i have to try other way such as instr and mid — Anthony, Nov 17 '17 at 11:41
If you're having unexpected crashes, [this answer](https://stackoverflow.com/a/40897483/7296893) provides a walkthrough on how to troubleshoot them. Personally, I think weird characters in the string might cause the issue. Check the `ContainsNonAscii` function I shared in [this answer](https://stackoverflow.com/a/44926401/7296893) (use `SELECT * FROM [table] WHERE ContainsNonAscii(barcode)` after adding that function to a module. If that returns records, they contain special characters) — Erik A, Nov 17 '17 at 12:05

Gustav · Answer 1 · 2017-11-17T11:58:41.243

0

Try being a bit selective:

update 
    table 
set 
    barcode = left(barcode, instr(barcode, "_") - 1)
where
    barcode like "*_*"

edited Nov 17 '17 at 11:58

answered Nov 17 '17 at 11:41

Gustav

The OP wants to remove `_?` anywhere, not just at the start. This will produce wrong results in any case where it does not occur at the beginning of the string. – underscore_d Nov 17 '17 at 11:51
it runs, access still working, but the result not as expected. – Anthony Nov 17 '17 at 11:57
stil stopped working, i think this access has something with instr. I tried to remove the instr so it became only left(barcode,4) and the access working just fine. – Anthony Nov 17 '17 at 12:03
My SQL runs fine here - and it should - so your installation may be hosed somehow. – Gustav Nov 17 '17 at 12:09
hmm, maybe. would u like to see the data sample? – Anthony Nov 17 '17 at 12:12
Yes please. If it can reveal something. – Gustav Nov 17 '17 at 13:00

Krish · Answer 2 · 2017-11-17T11:58:45.593

0

a better way would be using mid function

mid([barcode],1,instr([barcode],"_")-1)

and you should always have a where condition when updating: in your case something like WHERE [barcode] Like "*_*"

edited Nov 17 '17 at 11:58

answered Nov 17 '17 at 11:42

Krish

That throws an error for any string that doesn't contain `_`. Combine it with Gustav's answer, and two incomplete answers will make one complete one. – Erik A Nov 17 '17 at 11:46
no one updates the table without a where condition. I assumed checking wherther "_" present would go to the where condition!. in this case mid foundtion without IF() would work faster! :) :) @ErikvonAsmuth – Krish Nov 17 '17 at 11:49
Well, the query OP provides doesn't have a `WHERE`. Even if it's obvious to you, it might not be for him – Erik A Nov 17 '17 at 11:50
First I have tried Gustav, but we know the result not as expected. then I saw krish answer and i combine those 2. And you know what, access stopped working! god.. why??? – Anthony Nov 17 '17 at 11:55
@Anthony time to throw your pc out of the window. kidding :) Typical IT person would say, please restart your PC :D – Krish Nov 17 '17 at 12:01
1

haha.. in this situation even something like that worth to try lol. – Anthony Nov 17 '17 at 12:05

2 Answers2