trim before and after "-"

  • Thread starter Thread starter Irene
  • Start date Start date
I

Irene

i have a file which is export from UBS. i need to separate a field which is
contain PO no n item. how i can make it to two field?

eg in UBS:
PO No
PO-00007195-001
214002-024
IPO-00007143-003

i want the result come out in two field such like
PO No Item
PO-00007195 001
214002 024
IPO-00007143 003

can the access make it?

due to different customer have different style of the PO no. so the standard
things is the behind item no. all the item no will be in three digit. so can
we trim the three digit at the back as item, then the other is the PO no? but
i dont want the "-" before the item show out when i make it to two field.
anybody can help? thanks!
 
I would recommend doing this in 2 steps if you are updating the working
table

UPDATE [tablename]
SET Item = right([Po No],3)
WHERE yourconditions

UPDATE [tablename]
SET [Po No]= left([Po No],len([Po No])-4)
WHERE yourconditions

yourconditions is to limit the rows -- if you want to do them all, then
skip the WHERE clause

personally, I would import to a temporary table and parse the data as it
is moved to the working table

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day :)
*
 
hi strive4peace,

thanks for your help! i have make it.

strive4peace said:
I would recommend doing this in 2 steps if you are updating the working
table

UPDATE [tablename]
SET Item = right([Po No],3)
WHERE yourconditions

UPDATE [tablename]
SET [Po No]= left([Po No],len([Po No])-4)
WHERE yourconditions

yourconditions is to limit the rows -- if you want to do them all, then
skip the WHERE clause

personally, I would import to a temporary table and parse the data as it
is moved to the working table

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day :)
*


i have a file which is export from UBS. i need to separate a field which is
contain PO no n item. how i can make it to two field?

eg in UBS:
PO No
PO-00007195-001
214002-024
IPO-00007143-003

i want the result come out in two field such like
PO No Item
PO-00007195 001
214002 024
IPO-00007143 003

can the access make it?

due to different customer have different style of the PO no. so the standard
things is the behind item no. all the item no will be in three digit. so can
we trim the three digit at the back as item, then the other is the PO no? but
i dont want the "-" before the item show out when i make it to two field.
anybody can help? thanks!
Click to expand...
Click to expand...
 
hi Irene,

wonderful! happy to help ;)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day :)
*


hi strive4peace,

thanks for your help! i have make it.

strive4peace said:
I would recommend doing this in 2 steps if you are updating the working
table

UPDATE [tablename]
SET Item = right([Po No],3)
WHERE yourconditions

UPDATE [tablename]
SET [Po No]= left([Po No],len([Po No])-4)
WHERE yourconditions

yourconditions is to limit the rows -- if you want to do them all, then
skip the WHERE clause

personally, I would import to a temporary table and parse the data as it
is moved to the working table

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day :)
*


i have a file which is export from UBS. i need to separate a field which is
contain PO no n item. how i can make it to two field?

eg in UBS:
PO No
PO-00007195-001
214002-024
IPO-00007143-003

i want the result come out in two field such like
PO No Item
PO-00007195 001
214002 024
IPO-00007143 003

can the access make it?

due to different customer have different style of the PO no. so the standard
things is the behind item no. all the item no will be in three digit. so can
we trim the three digit at the back as item, then the other is the PO no? but
i dont want the "-" before the item show out when i make it to two field.
anybody can help? thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top