Expression

  • Thread starter Thread starter Robin Gilbert via AccessMonster.com
  • Start date Start date
R

Robin Gilbert via AccessMonster.com

I am trying to update some data using update query.
my field name is NSN, it contains data that reads as follow, 1111-22-333-
4444. I would like to update this data to read as follow, 223334444,
therefore removing the first 4 numbers and also removing the dashes.

Can someone please help !!

I am using Access 2000
 
Robin Gilbert via AccessMonster.com said:
I am trying to update some data using update query.
my field name is NSN, it contains data that reads as follow, 1111-22-333-
4444. I would like to update this data to read as follow, 223334444,
therefore removing the first 4 numbers and also removing the dashes.

Can someone please help !!

I am using Access 2000
Click to expand...

UPDATE SomeTable SET NSN = Replace(Mid(NSN,5),"-","")
 
I tried all three suggested expressions and it gives me an error syntax
incorrect. I'm testing it using Office2003 because thats what I have at
home, at work I use Office2000, could it be why ?

And I apologize for posting twice as I thought I was on a different forum
 
Now apparently its not enough. I updated that field fine thanks to you
guys. Now what I need to do is update a field called qty which has diffent
quanties in them but I need to make it 9 characters with leading 0s.

I.E I want qty 12 to read 000000012

Then to make it more difficult (my boss is demanding lol)I need to create a
new field and put them together

the field New NSN which now reads 112223333 and this 9 number quantity
000000012 to read 112223333000000012

I hope I explain this right and I hope it can be done it would save an
enormous amount of work.
 
Robin Gilbert via AccessMonster.com said:
Now apparently its not enough. I updated that field fine thanks to you
guys. Now what I need to do is update a field called qty which has diffent
quanties in them but I need to make it 9 characters with leading 0s.

I.E I want qty 12 to read 000000012

Then to make it more difficult (my boss is demanding lol)I need to create a
new field and put them together

the field New NSN which now reads 112223333 and this 9 number quantity
000000012 to read 112223333000000012

I hope I explain this right and I hope it can be done it would save an
enormous amount of work.
Click to expand...

Robin Gilbert,

String(9 - Len(YourString), "0") & YourString


Sincerely,

Chris O.
 
Robin Gilbert via AccessMonster.com said:
Now apparently its not enough. I updated that field fine thanks to you
guys. Now what I need to do is update a field called qty which has diffent
quanties in them but I need to make it 9 characters with leading 0s.

I.E I want qty 12 to read 000000012

Then to make it more difficult (my boss is demanding lol)I need to create a
new field and put them together

the field New NSN which now reads 112223333 and this 9 number quantity
000000012 to read 112223333000000012

I hope I explain this right and I hope it can be done it would save an
enormous amount of work.
Click to expand...

This expression will do it:

[New NSN] & Format(qty,"000000000")

Although I can't imagine why your boss wants such a hideous concatenated
field.
 
Back
Top