Hi friends,
I want to trim a string in a query but somehow I am not able to do it as I am not a ms access expert till now. How can I for example trim letter to etter? I want to trim the L character whenever it comes at the beginning?
Thanks for your help.

The attached DB will give you the idea. You can see it in both a form and a query.

This is putting all the characters to another field/texbox except when the first charcacter is a capital L, then it leaves the L behind.

Go to the form in design view and you will see what was entered in the unbound text box. Likewise, the query in design view will show you what was done.

Do something like this in a query:
Trimmed: iif(left(yourfield,1)= "L", mid(yourfield,2), yourfield)

You may have to replace all comma's with semi columns.

You need to look at the Left(), Right() and Mid() functions

Left("Alphabet",1) = "A"
Right("Alphabet",1) = "t"
Mid("Alphabet",3,3) = "hab"
Mid(""Alphabet",5) = "abet"

CodeMaster::cool:

It will be something like
Newfield:IIf(Left(yourfield,1)="L",Mid(Yourfield,2,Len(yourfield)-1),yourfield)

Explanation If left character of your field is L then newfield starts in position 2 and is 1 character less or don't alter field.

Brian

Newfield:IIf(Left(yourfield,1)="L",Mid(Yourfield,2,Len(yourfield)-1),yourfield)

Brian, the bolded part can be left out. If the third argument of the Mid function is empty/null/non-excistant it will by default take the remainder of "YourField"

Thanks for your answer, it solved my problem but now I have another one and I have to check for L as well as for LA,LB at the beginning.

How many more characters do you need to check for??

This is sounding more and more like a function.

Brian, the bolded part can be left out. If the third argument of the Mid function is empty/null/non-excistant it will by default take the remainder of "YourField"

I frequently leave it out but felt that a newbie ought to work to rule until he understood the system, however his expansion of the question makes a mess of that.
Why do they do that?

Brian

I did it with two queries first for L and then for the other two possibilities.

And then offcourse before long you want to take away LC...
Then a few months later LD...

Maintenance headache:(

Would

iif(left(yourfield,1)= "L", mid(yourfield,2), iif(left(yourfield,2) In("LA","LB"),mid(yourfield,3), yourfield))

work,
It would be not too difficult to modify the list

But agree
Maintenance headache

Brian

The previous post shows the problems that occur when a problem is added to by not being explained in full to start with, I just added the new request, but if the first IIF is satisfied the 2nd will never be tested :o, the order needs changing. As this would have been obvious from the first test, if not before, I assume the poster is happy with his 2 query approach but felt I ought to publish the correction incase others stumble across the thread.


Actually the poster also seems to run his 2 queries in the wrong order, if he trims off L then LA etc will never appear.

Brian