RTrim / LTrim query question

[Stinky Pete]

Arvo,

I've been reading about the above function, or similar, as it seems
applicable to my problem, however I have to admit I am totally lost.
What I need to do is update about 330 records that are currently in
the format "abc-xyz", all currently in a field called Type. I would
like to keep the "abc" text in the Type field and move/cut the "xyz"
text into a Description field. I only need to update the date once,
validate it is OK and then forget I ever did it ;-)

I think I'm making this harder than needed.

Regards,

Stinky Pete ;-)

[Tom van Stiphout]

On Mon, 12 Nov 2007 18:43:44 -0800, Stinky Pete
<Pe**********@symbionhealth.comwrote:

What do you mean by "I only need to update the date once,
validate it is OK"?
So far you're not working with date fields.
What kind of validation did you have in mind?

On another topic: a field named "Type" is a bad idea, because it is a
reserved word. You can only successfully use it if you always remember
to put square brackets around it.

-Tom.

>Arvo,

I've been reading about the above function, or similar, as it seems
applicable to my problem, however I have to admit I am totally lost.
What I need to do is update about 330 records that are currently in
the format "abc-xyz", all currently in a field called Type. I would
like to keep the "abc" text in the Type field and move/cut the "xyz"
text into a Description field. I only need to update the date once,
validate it is OK and then forget I ever did it ;-)

I think I'm making this harder than needed.

Regards,

Stinky Pete ;-)

[Benny Andersen]

On Mon, 12 Nov 2007 18:43:44 -0800, Stinky Pete wrote:

Arvo,

I've been reading about the above function, or similar, as it seems
applicable to my problem, however I have to admit I am totally lost.
What I need to do is update about 330 records that are currently in
the format "abc-xyz", all currently in a field called Type. I would
like to keep the "abc" text in the Type field and move/cut the "xyz"
text into a Description field. I only need to update the date once,
validate it is OK and then forget I ever did it ;-)

I think I'm making this harder than needed.

Regards,

Stinky Pete ;-)

Where comes ltrim or rtrim in the picture?

You can use split :
?split("abc-xyz","-")(0)
abc
?split("abc-xyz","-")(1)
xyz
--
Recards
Benny Andersen

[Rick Brandt]

Stinky Pete wrote:

Arvo,

I've been reading about the above function, or similar, as it seems
applicable to my problem, however I have to admit I am totally lost.
What I need to do is update about 330 records that are currently in
the format "abc-xyz", all currently in a field called Type. I would
like to keep the "abc" text in the Type field and move/cut the "xyz"
text into a Description field. I only need to update the date once,
validate it is OK and then forget I ever did it ;-)

I think I'm making this harder than needed.

Regards,

Stinky Pete ;-)

Trim functions are about removing leading and trailing spaces and have nothing
to do with your issue. Look at the help file for descriptions of the
functions...

Left() grabs text from the left of a string
Right() grabs text from the right of a string
Mid() grabs text from the middle of a string
InStr() returns the position of a character in a string

EX:

Left("abc-xyz", 3) - returns "abc"
Right("abc-xyz", 3) - returns "xyz"
Mid("abc-xyz", 2, 2) - returns "bc"
InStr(1, "abc-xyz", "-") - returns 4
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lyle fairfield]

"Rick Brandt" <ri*********@hotmail.comwrote in
news:aH*******************@newssvr14.news.prodigy. net:

Stinky Pete wrote:

>Arvo,

I've been reading about the above function, or similar, as it seems
applicable to my problem, however I have to admit I am totally lost.
What I need to do is update about 330 records that are currently in
the format "abc-xyz", all currently in a field called Type. I would
like to keep the "abc" text in the Type field and move/cut the "xyz"
text into a Description field. I only need to update the date once,
validate it is OK and then forget I ever did it ;-)

I think I'm making this harder than needed.

Regards,

Stinky Pete ;-)

Trim functions are about removing leading and trailing spaces and have
nothing to do with your issue. Look at the help file for descriptions
of the functions...

Left() grabs text from the left of a string
Right() grabs text from the right of a string
Mid() grabs text from the middle of a string
InStr() returns the position of a character in a string

EX:

Left("abc-xyz", 3) - returns "abc"
Right("abc-xyz", 3) - returns "xyz"
Mid("abc-xyz", 2, 2) - returns "bc"
InStr(1, "abc-xyz", "-") - returns 4

One brave, or foolish??? way to ltrim(,3) in SQL is to set the size of the
field to three.

In fooling with this, I learned something today:

using Northwind ...

Sub temp()
With DBEngine(0)(0)
On Error Resume Next
.Execute "ALTER TABLE Employees DROP Temp"
On Error GoTo 0
.Execute "ALTER TABLE Employees ADD Temp Text (50)"
.Execute "UPDATE EMPLOYEES Set Temp = LastName"
.Execute "ALTER TABLE EMPLOYEES ALTER TEMP Text (3)", dbFailOnError
End With
End Sub

Everything works with DAO.

Sub temp2()
With CurrentProject.Connection
On Error Resume Next
.Execute "ALTER TABLE Employees DROP Temp"
On Error GoTo 0
.Execute "ALTER TABLE Employees ADD Temp Text (50)"
.Execute "UPDATE EMPLOYEES Set Temp = LastName"
.Execute "ALTER TABLE EMPLOYEES ALTER TEMP Text (3)"
End With
End Sub

Not so with ADO.
The last sql "ALTER TABLE EMPLOYEES ALTER TEMP Text (3)"
fails returning a "field too small ..." error description.

I expect this is an argument for something, but I'm not sure what. I
suppose it depends upon whether you think the last SQL should run without
error.
--
lyle fairfield