Replace 2 characters in string with 0's

[kvar]

I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me.
I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code.
What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right.
Can someone please give me an example of how to do that or point me in the right direction?? To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.
Thank you in advance for any help!!

 

[MarkK]

Well, a couple of pieces of the puzzle are,
1) to find a fixed position in a string from the right, subtract a fixed number from the length of the original string, so . . .

dim pos as integer
pos = len(OrigString) - 5

2) To replace a substring within a string, you can use VBA.Mid(), like . . .

Mid(OrigString, pos, 2) = "00"

Hope this helps,

 

[CJ_London]

you can also use the strReverse function

newcode=strReverse(left(strReverse(oldcode),5) & "00" & mid(strReverse(oldcode),8))

 

[kvar]

It seems like strReverse would be the simpler way to go about it. Am I correct in thinking that I can't do this with an update query? If so, then in VBA I would basically do something like Update EA_ActiveLife_East.AgentNumber = strReverse(left(strReverse(AgentNumber),5) & "00" & mid(strReverse(AgentNumber),8))
Why do I feel like that is completely off base? I'm missing something I'm just not sure what it is.

 

[kvar]

Okay, I created an update query to update the AgentNumber to strReverse([AgentNumber]) so now that I have the numbers reversed all I need to do is update characters 6 & 7 to 0's. Not quite sure what the code would be to just replace those to characters no matter what they currently are though.
I already have one query to update the blank field PrimaryAgentNumber to equal the AgentNumber, then the query to reverse that string. And now I need one to replace the 2 characters with 0's, then I'll need to reverse it again to put it back in the right order. Definitely thinking that putting this in VBA would be alot faster than running 4 different queries.

 

[Brianwarnock]

Doesn't CJLONDO's code give you exactly what you want? He builds a string in reverse and then applies strReverse to put it back.
Run it in a query to check it

Brian

 

[CJ_London]

Just what I was going to say Brian

 

[kvar]

Okay, it did work so I just have the 2 queries, that one and the first one to copy AgentNumber into PrimaryAgentNumber. Which isn't bad, but the table has about 400,000 records so they do take a minute to run. It's not terrible but would definitely be better in VBA. But as long as it works!!
Thank you so much for your help!

 

[CJ_London]

not sure why you need two queries, if you are copying from one table to another then use the full solution on the copy or if you are updating another field n the same table you can do both at the same time.

400k records is not that many so sounds like you need to ensure your tables are indexed properly - unless your queries are doing a lot more or are in a network based back end I would have thought a few seconds.

VBA is unlikely to be quicker. I presume you have not put the conversion into VBA as a public function - that will be slow

 

[kvar]

Duh. I'm not sure why I have 2 queries either. Well I don't now, and everything works fine. So thank you.
It is still really slow for some reason. Compact and Repair, analyzed everything. Indexing seems fine. And I'm still developing so it's not split and is actually stored on my desktop right now. Very odd.

 

[MarkK]

I bet this would execute faster than reversing the string multiple times . . .

Mid(OrigString, len(OrigString) - 5, 2) = "00"

 

[CJ_London]

I bet this would execute faster than reversing the string multiple times . . .

I know you can use this in VBA, but how would you use it in a sql query? Other than creating a public function and calling it from the query - which would slow it down again.

 

[MarkK]

You're right, it doesn't work. And I timed that StrReverse() function and it is way faster than I thought. It can reverse a 10 char string, 300,000 times in 0.05 secs on my slow computer.