Extract text from a textfield, removing last 3 characters

[gmaster]

Hi, i need to write a function that removes the last 3 digits from a certain text field....i tried with various Left, Right, Trim but i couldn't do it....


Thanks

[ranman256]

You need a couple of functions:

= left(vWORD, LEN(vWORD)-3)

[gmaster]

Hey thanks, works great!
I'm also trying to use it making it pick the word from a combobox in a form:
=Left([Forms]![IntroA]![Combobox], Len([Forms]![IntroA]![Combobox])-3)
But in this case it gives me an error...you know why?

[John_G]

It would help if you said what the error was!

If the combo box has more than one column, with the first column hidden (this is very common), then [Forms]![IntroA]![Combobox] refers to the first (hidden) column, and not the one you see. Try something like [Forms]![IntroA]![Combobox].column(1) to refer to the second column.

John

[gmaster]

Yeah, sorry...so the error it gives me is "#Type?" or else "#Name?" (i'm using it in a textbox for a report), and the problem seems to be in the second part of the function (if i only use the Left() it works, but if i add the Len() or the InStr() it gives me those errors...)

[June7]

Is the length of the string variable? Show example data.

Is the combobox bound? Is it multi-column? Show the RowSource SQL.

[gmaster]

Yeah, the lenght is variable (Ex. Amil_BB, CMT_BB, Usher_BB, BRFA_BB.....as you can see, i was trying to get the name without the "_BB"), while the combobox isn't bound and single-column (this is its SQL: SELECT [PatList].[PatName] FROM PatList ORDER BY [PatName])

[John_G]

Hi -

If the part to be removed is always "_BB", then you can use the replace function:

replace( [Forms]![IntroA]![Combobox],"_BB","")


In the report, try using VBA to populate the textbox: [textboxname] = Left([Forms]![IntroA]![Combobox], Len([Forms]![IntroA]![Combobox])-3)


The form has to be open for this to work.

[gmaster]

Thanks a lot everyone, i managed to make it work!!