Trimming in MS Access ?

[knowledge76]

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.

 

[Mike375]

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.

 

[namliam]

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.

 

[DCrake]

Simple Software Solutions

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:

 

[Brianwarnock]

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

 

[namliam]

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"

 

[knowledge76]

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.

 

[namliam]

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

This is sounding more and more like a function.

 

[Brianwarnock]

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

 

[knowledge76]

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

 

[namliam]

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

Maintenance headache

 

[Brianwarnock]

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

 

[Brianwarnock]

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 , 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