Need TRIM Advice!

[fhs]

I have an Address field with data like "235 Elm Street (ZIP2345)." In a query, I want to trim off the nine right-most characters, the (ZIP2345). What's the trick with TRIM? I've tried New Address: RTrim([Address],9), but this isn't working. Sure appreciate any help.

 

[Mile-O]

I have an Address field with data like "235 Elm Street (ZIP2345)." In a query, I want to trim off the nine right-most characters, the (ZIP2345). What's the trick with TRIM? I've tried New Address: RTrim([Address],9), but this isn't working. Sure appreciate any help.

Have you actually read the Access Help on why you would use a Trim() function? You only use it to remove excess spaces from either end of a string.
To get the rightmost nine characters you would use the Right() function.

 

[fhs]

Thanks, but I want to get rid of the nine right-most characters.

 

[Mile-O]

Left(MyField, Len(MyField) - 9)

 

[fhs]

I tried this and I got a "Data type mismatch in criteria expression" error message. The data is in a text format. Why this?

 

[Mile-O]

Left(MyField, Len(MyField) - 9)

I left a bracket off...


You should have this:

Left$([Address],Len([Address]-9))

Do you?

 

[NimbusSoftware]

Error explained

If you use Left$, Mid$, Right$ or Trim$ as a function in a query you will receive a Data type mismatch error if the a field is null. The $ in the function dictates that a string is returned. If you use Left, Mid, Right or Trim a variant is returned and you won't recieve the error.

 

[fhs]

I know you're working hard at this, but I'm still getting the "Data mismatch..." message. My query expression is:Left$([Address],Len([Address]-9)). What dya think?

 

[fhs]

I also removed the $ and still got the "Data mismatch..." error in this query: Left([Address],Len([Address]-9)). ???

 

[NimbusSoftware]

You'll also receive an error when:

str="asdfgf"
Len(str)=6
Left(str,len(str)-9)= ERROR

because len(str)-9 is a negative number.

 

[Superock]

Some explanation pls

I left a bracket off...


You should have this:

Left$([Address],Len([Address]-9))

Do you?

I know that your method works (I have found that usually this is the best method to use when parsing strings) But I don't quite understand why it works. I mean I know how the left() function works but what exactly is Len() doing?

BTW could you not just do a Right([Address],0) ??

 

[NimbusSoftware]

Len() question

Len() is the length of the string. So if you want the all but 5 characters from the left side of a string but don't know the length of the string you can use:

str="abcdefgh"
Left(str, len(str)-5) -> "abc"

btw, right("abcdefgh",0) yields an empty string because your returning zero characters from the right side of the string.

 

[NimbusSoftware]

Error explained-edited

You will receive an error if you apply Left$, Mid$, Right$ or Trim$ on a field that is null.

 

[Superock]

Len() is the length of the string. So if you want the all but 5 characters from the left side of a string but don't know the length of the string you can use:

str="abcdefgh"
Left(str, len(str)-5) -> "abc"

btw, right("abcdefgh",0) yields an empty string because your returning zero characters from the right side of the string.

So Then

str="abcdefgh"
Right(str, len(str)-5) Would be -> "fgh"


Hmmmm, how would this be applied to capturing certain unknown characters from the middle of a string?

 

[NimbusSoftware]

Right(str, len(str)-5) Would be -> "fgh"

yep

Hmmmm, how would this be applied to capturing certain unknown characters from the middle of a string?

Never tried that. Let's do an experiment.

str="abcdefgh"
Mid(str,len(str)/2,len(str)/4)

The helpless file says:

Mid([I]string[/I], [I]start[/I][, [I]length[/I]])

Therefore the above experssion would produce "de" (len(str)/2 for the starting character and len(str)/4 for the number of characters to return)