Need formula in query to return part of string after blank space

[Joe M.]

I have a query that needs to return the part of the original string after the
first blank. In other words I want to cut off the first word as shown below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.

 

[kc-mass]

Try this:

Short_Desc: IIF(instr(Field1, " ")

0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"

")+1,35),[Field1]))

Regards

Kevin

I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.

 

[Al Campagna]

Joe,
Try...
=IIF(InStr(Fld1," "),Mid(Fld1,InStr(Fld1," ")+1), Left(Fld1,Len(Fld1)))
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[stumac]

Hi Joe, try:

Short_Desc: Mid([field1],InStr(1,[field1]," ")+1)

Hth

Stu

 

[Al Campagna]

kc-mass,
The OP wasn't very clear as regards "The."
I went by the "examples given" and the "desired results"...
Your calculation looks for a "The" in each string.

I think the OP meant that if there are ANY two words, separated by a
space...
return the second word. If no space... return the whole string.

Al Campagna

Try this:

Short_Desc: IIF(instr(Field1, " ")

0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"

")+1,35),[Field1]))

Regards

Kevin

I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I
thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.

 

[Joe M.]

Hi. I will try that. In the meantime, I found another solution that works:

Short_Desc2: Right([Field1],Len([Field1])-InStr([Field1]," "))

Hi Joe, try:

Short_Desc: Mid([field1],InStr(1,[field1]," ")+1)

Hth

Stu

I have a query that needs to return the part of the original string after the
first blank. In other words I want to cut off the first word as shown below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.

 

[kc-mass]

Sorry. I copied the original and did not complete the editing

Regards

kc-mass,
The OP wasn't very clear as regards "The."
I went by the "examples given" and the "desired results"...
Your calculation looks for a "The" in each string.

I think the OP meant that if there are ANY two words, separated by a
space...
return the second word. If no space... return the whole string.

Al Campagna

Try this:

Short_Desc: IIF(instr(Field1, " ")

0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"

")+1,35),[Field1]))

Regards

Kevin

I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I
thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.