Issue adding text to a field

[mkdrep]

I have a text field with numbers like this: 2014556682 or 2014236781, etc.
My problem is that I need to insert 00 between the 2014 and the last 6 numbers. :banghead:

Any suggestions as to how I can do this.

thank you in advance........Mark

 

[jdraw]

Can you tell us WHAT the number represents and WHY you need to insert some 00 chars?

2014 looks an awful lot like a date and Access has a Date/Time datatype meant just for such things.

 

[mkdrep]

Glad to.....

When I first developed this Access Database it was back in the late 1990's! At that point in time the 201400565333 numbers would have been imported into my database as 14- 565333. That is why I had to set the field up as a text field and no a number field. (I imported the numbers from a company called FW Dodge which is a company that tracks all the construction work going on in my territory. They were originally 14- 565333 numbers. They have only recently changed to sending them as 201400565333).

In essence I have to change all the numbers from 14- 565333 by deleting the (-), changing the 14 to 2014 and adding 00 between 2014 and 565333...AND I have to do this to 6,500 different records! lol Unfortunately, I didn't know I had to add the 00 until after I had already made the change from 14- 565333 to 2014565333. Hence the reason I have to add 00 between 2014 565333. (Aren't you glad you asked!)

Thanks....Mark

 

[Galaxiom]

Left([field],4) & "00" & Mid([field],5)

 

[mkdrep]

Left([field],4) & "00" & Mid([field],5)

Thank you for this info! Just one question, since the field currently has 10 characters in it, i.e. 2014123456, and there are 6 characters after the original 2014 should the formula be:
Left([field],4) & "00" & Mid([field],6)[/QUOTE]

Just want to make sure I get this right this time!

Thank you for your help....Mark

 

[pr2-eugin]

Mid function works different to that of Left.

However you can go with Right Function with 6 as argument. They both i.e. Mid with argument 5 and Right with argument 6 will give the same result.

? Left("2014123456", 4) & "00" & Mid("2014123456", 5)
201400123456
? Left("2014123456", 4) & "00" & Right("2014123456", 6)
201400123456

But Mid with 6 will give the wrong result.

? Left("2014123456", 4) & "00" & Mid("2014123456", 6)
20140023456