Trim text

[Sylviajb]

Hi
I need to trim text with a space between two words but then only the first part is returned e.g
Field Name: CMDESC - Text "Other Complication"
When I use: Compl: Left([CMDESC],InStr(1,[CMDESC]," ")-1) it only returns "Other"
How do I get it to return "Other Complication"

 

[jdraw]

Just refer to CMDesc if that's the field that houses "Other Complication".

If you want it without the space eg "OtherComplication", then use a Replace


MyText =Replace(CMDesc," ","")

 

[Sylviajb]

Tx but I need it to return "Other Complication"

 

[jdraw]

If your field CMDesc contains the value "Other Complication"

The just use CMDesc

Tell us in plain English what you are trying to do.
Return from what? Where is CMDesc defined and located?

You seem to be wandering a little from your first post
I need to trim text with a space between two words

 

[Sylviajb]

tblStats26_TopCostingand Compl_01CMDESCOther Complication Other Complication
This is the field. After "Other Complication" there are about 80 spaces I want to trim of.
When I use: Compl: Left([CMDESC],InStr(1,[CMDESC]," ")-1) it only returns "Other" in stead of "Other Complication"
I don't know how to put it more clearly

 

[MSAccessRookie]

tblStats26_TopCostingand Compl_01CMDESCOther Complication Other Complication
This is the field. After "Other Complication" there are about 80 spaces I want to trim of.
When I use: Compl: Left([CMDESC],InStr(1,[CMDESC]," ")-1) it only returns "Other" in stead of "Other Complication"
I don't know how to put it more clearly

Allow me to clarify what you are actually doing, so that you may better understand why your program is acting the way that it is.

You said that you start with something like

CMDESC: "Other Complication {80 Spaces }"

Left([CMDESC],InStr(1,[CMDESC]," ")-1)

• InStr(1,[CMDESC]," ") Will Locate the First Space (The one after Other)
• Left([CMDESC],InStr(1,[CMDESC]," ")-1) will result in all characters up to but not including the First Space.

You have indicated that you want to want to remove the Spaces that are on the Right side of your String. To do that, there is a Function RTRIM that could give you what you are looking for.

-- Rookie

 

[jdraw]

Maybe someone else will understand what you are to do.
Are you saying, and this is just a guess on my part, that you have 2 occurrences of Other Complication in the field CMDesc?

If so then go back to your Original statement and Use " O"
Compl: Left([CMDESC],InStr(1,[CMDESC]," O")-1)


To get rid of leading and/or trailing spaces use Trim(Your field name)

 

[marlan]

Hi
It sounds to me too you need the Trim() or rTrim() functions.
Otherwize, if you want the second space, you could try:

Left([CMDESC],inStr( InStr(1,[CMDESC]," "),[CMDESC]," ")-1)

In other words: look for the first space comming rite after the first space...

Good luck!