Trim

[Guest]

How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[Jeff Boyce]

Use Left(), Mid(), Right() and/or Instr() functions. Check Access HELP for
examples and syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[doodle]

=Left([FieldName],(Len([FieldName])-(Right([FieldName],5))))


-doodle

 

[Douglas J. Steele]

That won't work. I think you meant

=Left([FieldName],(Len([FieldName])-5)))

(Right([FieldName],5) will result in a 5 character string, which you cannot
subtract from Len([FieldName])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

=Left([FieldName],(Len([FieldName])-(Right([FieldName],5))))


-doodle

Use Left(), Mid(), Right() and/or Instr() functions. Check Access HELP
for
examples and syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

 

[JK]

Chey,

If understand you correctly , you want to *eliminate* everything to *right
of the dash (-) including the dash eg :

"Alaska Air-Pd by ???" becomes "Alaska Air"

If this is the case use:

YourString = Trim(Left(YourString, InStr(1, YourString, "-") - 1))

(The Trim function is to eliminate space at the end if the result if the
original string is "Alaska Air - Pd by ???", without it you will get
"Alaska Air " (space at the end)

Regards/JK

 

[Guest]

This didn't work for me
Here are some example of what shows up before and then after
Era Aviation-To Be Pd Era Aviation-To Be Pd
Parking Pa
Payless Kenai Payless

If there is no - then the whole word this is how I would like it to appear
Era Aviation-To Be Pd Era Aviation
Parking Parking
Payless Kenai-Pd By CCB Payless Kenai
Car Rental-Will be Reimburssed Car Rental

Thanks for your help
Chey

That won't work. I think you meant

=Left([FieldName],(Len([FieldName])-5)))

(Right([FieldName],5) will result in a 5 character string, which you cannot
subtract from Len([FieldName])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

=Left([FieldName],(Len([FieldName])-(Right([FieldName],5))))


-doodle

Use Left(), Mid(), Right() and/or Instr() functions. Check Access HELP
for
examples and syntax.

Regards

Jeff Boyce
Microsoft Office/Access MVP


How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[JK]

Great minds think alike, Van ;-)

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

--
HTH
Van T. Dinh
MVP (Access)

How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[Guest]

So it worked for everything that had a "-" but the ones that do not #Error
appears
So Parking #Error
Airfare #Error
all of them Although the one with the "-" looks good.
any more suggestions?

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

--
HTH
Van T. Dinh
MVP (Access)

How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[JK]

OK, change it to:

= IIf(InStr(1, [FieldName], "-") = 0, [FieldName], Left([FieldName],
InStr(1, [FieldName], "-") - 1))

Regards/JK

So it worked for everything that had a "-" but the ones that do not #Error
appears
So Parking #Error
Airfare #Error
all of them Although the one with the "-" looks good.
any more suggestions?

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

--
HTH
Van T. Dinh
MVP (Access)

How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[David F Cox]

= Left([FieldName] & "-", InStr([FieldName] & "-", "-") - 1)

So it worked for everything that had a "-" but the ones that do not #Error
appears
So Parking #Error
Airfare #Error
all of them Although the one with the "-" looks good.
any more suggestions?

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

--
HTH
Van T. Dinh
MVP (Access)

How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey

 

[Arvin Meyer [MVP]]

Good but this will work just as well:

= Left([FieldName], InStr([FieldName] & "-", "-") - 1)

Go for simplicity. You only really need to cover the empty string in the
InStr section.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

= Left([FieldName] & "-", InStr([FieldName] & "-", "-") - 1)

So it worked for everything that had a "-" but the ones that do not
#Error
appears
So Parking #Error
Airfare #Error
all of them Although the one with the "-" looks good.
any more suggestions?

It sounds to me that you want:

= Left([FieldName], InStr([FieldName], "-") - 1)

--
HTH
Van T. Dinh
MVP (Access)



How do I trim everything -and to the right
So if I have Hyatt-Pd By ???
I want to Trim -Pd by???
then I may have Alaska Air-Pd By???
I want to trim Pd by???
I only want before the -
Thanks
Chey