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
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
You need a couple of functions:
= left(vWORD, LEN(vWORD)-3)
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?
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
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...)
Last edited by gmaster; 10-07-2014 at 03:38 AM.
Is the length of the string variable? Show example data.
Is the combobox bound? Is it multi-column? Show the RowSource SQL.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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])
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.
Thanks a lot everyone, i managed to make it work!!