Add one space before the last charecter

[ardy]

Hello All,
I am trying to construct a update Query to add a space right before the last character. but am having a hard time:banghead:.... can anybody point me to the right direction.


Example:
Book
Door
Sliding Door
Jam


Should look like this after the Query



Boo k
Doo r
Sliding Doo r
Ja m



Any help is appreciated.....

 

[pbaldy]

One way (Immediate window test):

?left("blah blah",len("blah blah")-1) & " " & right("blah blah", 1)
blah bla h

 

[ardy]

Thank you for the response.....Tried


Left([T_lab_anl_method_name],-1) & " " & Right([T_lab_anl_method_name],1)


it gives me can't update all the records violation error. but if I take the negative out it works but the result is not what i was aiming for.


Ardy

 

[MajP]

I am assuming that was a typo. I think he meant the length of the field minus 1

Left([T_lab_anl_method_name],len([T_lab_anl_method_name])-1)

 

[isladogs]

You didn't copy Paul's code correctly. Try this:

Left([T_lab_anl_method_name],Len([T_lab_anl_method_name])-1) & " " & Right([T_lab_anl_method_name],1)

 

[pbaldy]

No typo, it was there and missed:

One way (Immediate window test):

?left("blah blah",len("blah blah")-1) & " " & right("blah blah", 1)
blah bla h

Send garlic.

 

[ardy]

What if the length varies, i.e


Book
Door
Sliding Door
Jam

 

[pbaldy]

Have you tried it? The Len() function accounts for varying length. That said, an empty field could be a problem.

 

[ardy]

Oh.... I got it......
Never mind it works perfect......... Thank you all


Left([T_lab_anl_method_name],Len([T_lab_anl_method_name])-1) & " " & Right([T_lab_anl_method_name],1)

 

[pbaldy]

Happy to help.

 

[isladogs]

As Paul mentioned, that code will fail if your field is empty.
Here's one way of handling that issue

IIf(Len([T_lab_anl_method_name])>0,Left([T_lab_anl_method_name],Len([T_lab_anl_method_name])-1) & " " & Right([T_lab_anl_method_name],1),"")