Function to elimante words o seprated after dush

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate it.

could you help me on this please.
 
What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).
 
Hi Douglas,

thank for your answer ,I have a problem writting the Update statement, could
you help me please.




--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).
Click to expand...
 
UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ldiaz said:
Hi Douglas,

thank for your answer ,I have a problem writting the Update statement, could
you help me please.




--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ldiaz said:
Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ldiaz said:
Hi Douglas,

thank for your answer ,I have a problem writting the Update statement, could
you help me please.




--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
DoCmd.RunSQL "UPDATE Size_tbl" & _
"Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"

Hopefully that'll survive word-wrap...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ldiaz said:
here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I
believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ldiaz said:
Hi Douglas,

thank for your answer ,I have a problem writting the Update statement, could
you help me please.




--
Lorenzo Díaz
Cad Technician


:

What's in front of the dash will be Left([Name_Size],
InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size],
"-") + 1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate
it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
Oops. Just noticed there's a space missing between the name of the table and
the SET keyword. It should be:

DoCmd.RunSQL "UPDATE Size_tbl " & _
"SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
DoCmd.RunSQL "UPDATE Size_tbl" & _
"Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"

Hopefully that'll survive word-wrap...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ldiaz said:
here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


Douglas J Steele said:
UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I
believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

thank for your answer ,I have a problem writting the Update statement,
could
you help me please.




--
Lorenzo Díaz
Cad Technician


:

What's in front of the dash will be Left([Name_Size],
InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size],
"-") +
1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate
it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
Hi Douglas, Thank you so much for your help, now it;s working fine ..

I had a problem with the criteria, but I have omitted that step,

Thank you again.

I have ckicked YES in:
Did this post answer the question?

--
Lorenzo Díaz
Cad Technician


Douglas J. Steele said:
Oops. Just noticed there's a space missing between the name of the table and
the SET keyword. It should be:

DoCmd.RunSQL "UPDATE Size_tbl " & _
"SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
DoCmd.RunSQL "UPDATE Size_tbl" & _
"Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"

Hopefully that'll survive word-wrap...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ldiaz said:
here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


:

UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I
believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

thank for your answer ,I have a problem writting the Update statement,
could
you help me please.




--
Lorenzo Díaz
Cad Technician


:

What's in front of the dash will be Left([Name_Size],
InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size],
"-") +
1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how separate
it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
Sorry about that: the last line should have been:

"WHERE InStr([Name_Size], '-') > 1"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ldiaz said:
Hi Douglas, Thank you so much for your help, now it;s working fine ..

I had a problem with the criteria, but I have omitted that step,

Thank you again.

I have ckicked YES in:
Did this post answer the question?

--
Lorenzo Díaz
Cad Technician


Douglas J. Steele said:
Oops. Just noticed there's a space missing between the name of the table
and
the SET keyword. It should be:

DoCmd.RunSQL "UPDATE Size_tbl " & _
"SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
DoCmd.RunSQL "UPDATE Size_tbl" & _
"Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"

Hopefully that'll survive word-wrap...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


:

UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I
believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

thank for your answer ,I have a problem writting the Update
statement,
could
you help me please.




--
Lorenzo Díaz
Cad Technician


:

What's in front of the dash will be Left([Name_Size],
InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size],
"-") +
1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how
separate
it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
Yes, that works very fine.

Thank you for your support.
--
Lorenzo Díaz
Cad Technician


Douglas J. Steele said:
Sorry about that: the last line should have been:

"WHERE InStr([Name_Size], '-') > 1"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ldiaz said:
Hi Douglas, Thank you so much for your help, now it;s working fine ..

I had a problem with the criteria, but I have omitted that step,

Thank you again.

I have ckicked YES in:
Did this post answer the question?

--
Lorenzo Díaz
Cad Technician


Douglas J. Steele said:
Oops. Just noticed there's a space missing between the name of the table
and
the SET keyword. It should be:

DoCmd.RunSQL "UPDATE Size_tbl " & _
"SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


DoCmd.RunSQL "UPDATE Size_tbl" & _
"Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
"[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
"WHERE InStr([Name_Size], "-") > 1"

Hopefully that'll survive word-wrap...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


here is my code and it does not work.


DoCmd.RunSQL "UPDATE Size_tbl"
Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
[Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 1


do you know why?

the field are as Text in the table.
and I named as Size1 and Name1
Thanks
ld
--
Lorenzo Díaz
Cad Technician


:

UPDATE MyTable
SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
[Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
WHERE InStr([Name_Size], "-") > 0

Note that Name and Size are not good names for fields in tables: I
believe
they're both reserve words.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

thank for your answer ,I have a problem writting the Update
statement,
could
you help me please.




--
Lorenzo Díaz
Cad Technician


:

What's in front of the dash will be Left([Name_Size],
InStr([Name_Size],
"-") - 1).

What's after the dash will be Mid([Name_Size], InStr([Name_Size],
"-") +
1).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a Table with information like this,

Name_Size
FELSEN 1272-LL/LX-1L
FRACA 2031-LR/LSX+2
GALLADORA 2288-LR/LSX+2

but now I want to separare it in two column

Name Size
FELSEN 1272- LL/LX-1L
FRACA 2031- LR/LSX+2
GALLADORA 2288- LR/LSX+2

I want it sperated after of the dash.
I know that I need to run an SQL update but I don't how
separate
it.

could you help me on this please.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Best way to switch to single user W/O passwords?? 2
How to convert Word ot Text Article to Excel? 1

Back
Top