Using A Trim and Right Function together

[kschomer]

I have a file I receive from an outside vendor monthly. The problem is the address is ADR1, ADR2, ADR3, ADR4 and ADR5. But, depending on the Address you never know what Address field the State and Zip code will be in. Therefore, I have concatenated Adr1-5 into another field. Now I need to determine what the STATE is. I also need to TRIM the fields of blank space. So I want to do a TRIM(Concatenate) and then I want to use the RIGHT(Concatenate,7,2) I have accomplished the TRIM, but when I try to use the Right from a new Query the Trim is not recognized. What would the procedure be or can I compound these two functions at one time and what is the Syntax?

 

[GDGarth]

It looks to me like you've got the wrong number of arguements for your "Right" function. It should be:

Right(StringExpression,# of characters)

Did you try:

Right(Trim([YourFieldName]),#of characters)

That should get you X characters from your string after trimming the spaces.

If you need to pull characters out of the middle of the string, try "Mid". With Mid, you need to specify where to start and how many characters you want.

 

[kschomer]

Trim function alone gives me, for example:
12345 South Main Street Anytown, CA98765

Right(Trim([Table Name]![Concatented field]),7)

This returns 1 record without any data in any of the fields.

I expect the query to return, as follows
CA98765
TX23456
AL76543
etc. for example.

I am not able to use a Mid since the starting position of the State varies depending on the entire address (concatenated)

 

[missinglinq]

As to the use of the Mid function to return the state: Assuming you trimmed your fields first, then concatenated them, and always used the regular zip code (not the extended zip), the start of your "Mid" function for the state designation would be would be the length of your concatenated string - 6 characters. Thus,

strState=Mid(ConcatenatedString, (Len(ConcatenatedString)-6) ,2) should always return the state designation.

I'm confused about exactly what you mean by "This returns 1 record without any data in any of the fields." Please clarify.

TRhe Missinglinq

"It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[kschomer]

I ended up using the following:

Address: Trim([ADR1]&[ADR2]&[ADR3]&[ADR4]&[ADR5])- this trimmed off the leading/preceeding spaces.

Expr1: Len([Address])

State: Mid([Address],[Expr1]-6,2)

This seemed to work when the Right function would not. Thanks for your help. Karen