Extracting data and Reformat

  • Thread starter Thread starter Lisa W.
  • Start date Start date
L

Lisa W.

I am trying to create a new data field but need to add dashes to the current
field's format.

See below:

Original Format – 140 0 03 01

I am trying to accomplish: 140-0-03-01 using the below criteria

TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)

....but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).

Can someone help?

Thanks in Advance,
Lisa W.
 
I am trying to create a new data field but need to add dashes to the current
field's format.

See below:

Original Format - 140 0 03 01

I am trying to accomplish: 140-0-03-01 using the below criteria

TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)

...but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).

Can someone help?

Thanks in Advance,
Lisa W.
Click to expand...

if the area between the numbers is a space use
REPLACE
instead
http://www.techonthenet.com/access/functions/string/replace.php
 
Lisa W. said:
I am trying to create a new data field but need to add dashes to the
current
field's format.

See below:

Original Format – 140 0 03 01

I am trying to accomplish: 140-0-03-01 using the below criteria

TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)

...but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).

Can someone help?

Thanks in Advance,
Lisa W.
Click to expand...
 
Karl,

Where would I put that? Within the criteria I already set up or someplace
differently??



KARL DEWEY said:
Try this ---
Replace([TN], " ","-")
--
KARL DEWEY
Build a little - Test a little


Lisa W. said:
I am trying to create a new data field but need to add dashes to the current
field's format.

See below:

Original Format – 140 0 03 01

I am trying to accomplish: 140-0-03-01 using the below criteria

TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)

...but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).

Can someone help?

Thanks in Advance,
Lisa W.
Click to expand...
Click to expand...
 
Got it! Thanks so much!! I was struggling with this and didn't realize it
was that easy!!!

Very HELPFUL!!! :-)

Lisa W.

KARL DEWEY said:
Try this ---
Replace([TN], " ","-")
--
KARL DEWEY
Build a little - Test a little


Lisa W. said:
I am trying to create a new data field but need to add dashes to the current
field's format.

See below:

Original Format – 140 0 03 01

I am trying to accomplish: 140-0-03-01 using the below criteria

TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)

...but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).

Can someone help?

Thanks in Advance,
Lisa W.
Click to expand...
Click to expand...
 
Karl,

Where would I put that? Within the criteria I already set up or someplace
differently??



KARL DEWEY said:
Try this ---
Replace([TN], " ","-")
Click to expand...
I am trying to create a new data field but need to add dashes to the current
field's format.
See below:
Original Format - 140 0 03 01
I am trying to accomplish: 140-0-03-01 using the below criteria
TN: Left([TN],3) & "-" & Mid([TN],5,4) & "-" & Right([TN],2)
...but I am getting: 140-0 03-01 (I can't figure out how to remove the
space; I need a dash there).
Can someone help?
Thanks in Advance,
Lisa W.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

in your update query

create a query

add the field (TN)

change the query to an Update query (the tool button that has two
spread sheets [next to the run exclamation point], change it to a
pencil exclamation point)

change the update criteria to Karl's script

run the query
 
Back
Top