I have an unbound textbox control on the header of a continuous form. I use the On Change event to trigger code which grabs what ever is typed in to reset the forms RecordSource. (me.RecordSource = "SELECT * FROM ClientT WHERE Company like '*" & me.Findtxt & "*' ORDER BY Company"

Works fine unless user types a space. No matter what I do, the space is removed by the code before it can be used in the Select statement. Do you know a way around this?

---

Reply from Richard Rost:

Is the space before/after the text or within it? If it's outside, that sounds like a TRIM is running, which happens by default with most text boxes in Access - to prevent accidental spaces, like after a FirstName field. I don't know of a way to turn this behavior off. It's not in the AutoCorrect options (File > Options > Proofing).

I did a quick Google search and from what I can tell, there's no way around this (yeah, kinda lame, I agree). I would recommend selecting another character like _ and then teaching your users to use that to search for a space. In your code you could then say:

X = Me.FindTxt
X = Replace(X,"_"," ")

Then use X for your SQL where condition. I'll post this to my Access MVP email group and see if anyone on there has a better solution.

My email to them:

---

Hey guys.

I have a student who just asked me a great question: is it possible to STOP Access from trimming trailing spaces from a text box? I know that's generally the way you want Access to behave (to prevent first names like "Joe ") but in this particular case he's building a search form and he wants the option to have a trailing space in his search criteria. For example, he may want to search for "Jo " and not get values returned like "Joseph".

After some searching I can't find a way to turn this option off. It's not in the Autocorrect options, nor the properties for text boxes. I recommended for now just using an underscore and then REPLACE-ing that in code to a space character, but that's a cheesy solution.

Any thoughts?

---

I'll be sure to share any useful replies.