Solved Capturing the fact that a user has entered a string ending in a space

[Alc]

I have a form that is used to enter search criteria relating to car warranty claims.
One of the fields is an unbound text box that allows the user to enter a specific keyword.
The problem I have is that Access removes any trailing spaces from any entered word and there are times when the space means the difference between a few correct 'hits' and lots of incorrect ones.

For example, if the user enters "FIRE", they will get results that include "FIREPROOF", "FIRELINE" (a brand), and so on.
They want to be able to enter " FIRE ", to just get cases where the word appears in the middle of a sentence, but this gets changed to " FIRE".

I've tried using the Before and After Update events to capture the entered value, but both return the word after the trailing space has been removed.

I've found a few people reporting the same problem online, so I'd be surprised if nobody has come up with a workaround for this 'feature'.

The workaround for now is to enter "FIRE *", as the wildcard allows the space to be retained, but we have a new manager and he's insisting I find a way to save the users from the 'hassle' of having to do this i.e. he keeps forgetting to do it.

Is there some other event I could use that would capture this value before Access removed the space?

 

[theDBguy]

You could try using the Change or the KeyUP event of the unbound textbox to store the keyword in a variable and then use that in your search.

 

[Alc]

You could try using the Change or the KeyUP event of the unbound textbox to store the keyword in a variable and then use that in your search.

Thanks for the reply.

I added

debug.print Keyword

behind both events, but if I enter "test ", both are capturing "test".

 

[jdraw]

You could trim the search term to remove leading/trailing spaces.
searchterm=Trim(searchterm)

Update: I reread post #1 and this, although it would remove the space(s), does not help with the issue you identified.

My gut feel is that your current code is TRIMming or RTRIMming the search keyword.
You could adjust the current code to not use any trimming, then accept the search term as entered.

You could check to see if the last character entered was a space.
If right(searchterm," ") then, do the search.

 

[Gasman]

Why not just trim the field and add the space and * yourself.
Debug the length, as both your results would look the same?

 

[Alc]

Why not just trim the field and add the space and * yourself.
Debug the length, as both your results would look the same?

This is what I want to do.

However, not all the words end in a space. In some cases the users do want to detect something if it comes up as part of a word as well as on it's own e.g. "OVERHEAT" should get hits on both "OVERHEAT" and "OVERHEATED", while "FIRE " should not get hits on "FIRED" or "MISFIRED"

In order to add the space and asterisk myself, I need to know whether the user intended a trailing space.

 

[MajP]

I would have an option button next to the textbox
-Search whole words only
-Search Anywhere in word

*Searchword*
or
"Searchword *" OR "* Searchword *" Or "* Searchword"

 

[jdraw]

You may have to add to your search dialog to ensure the process does what the user intended.
This would involve asking/confirming whether or not
-search the exact word/term
-search with a specific stem Fire*


Ah ha! I see MajP has just added what I was trying to type

 

[Alc]

Thanks MajP and jdraw.
On one hand, it's good to know I was thinking along the same lines.

However, the same manager shot this down when I suggested it , since (quote) "It's still extra work for the user".

I was hoping there was some way to detect the entry before Access 'fixes' it, store it, then use it later.

 

[MajP]

However, the same manager shot this down when I suggested it , since (quote) "It's still extra work for the user".

I would find that hard to believe that the average user would even think that they need to add a space before and after a word to get whole words. That argument does not seem logical.
I think you would actually save them time with the options.

 

[Alc]

I would find that hard to believe that the average user would even think that they need to add a space before and after a word to get whole words. That argument does not seem logical.
I think you would actually save them time with the options.

You may well be right, but I'm not in a position to refuse to do it, unfortunately.
My options are limited to doing what the manager wants or demonstrating why it's not possible.

 

[theDBguy]

Thanks for the reply.

I added

debug.print Keyword

behind both events, but if I enter "test ", both are capturing "test".

Just curious, can you show us the code you tried?

 

[Josef P.]

Let me understand:
The user enters " fire " and then only the marked hit should come:

• "... fire ..." <-- only this is a result
• ".. fireline ..."
• ".. fire."
• "fire ..."
• ".. fire, ..."

 

[theDBguy]

I just quickly tried this:

Private Sub Text0_Change()
Keyword = Me.Text0.text
MsgBox "*" & Keyword & "*"

End Sub

and got this after five keystrokes:

 

[Gasman]

I was about to ask if you could check the last character of the .Text property

 

[Alc]

Just curious, can you show us the code you tried?

Yep. If it's something simple I'm doing wrong, I'll be delighted.
Each of the following returned the same for me.

Private Sub Keyword_Change()
    Debug.Print Keyword
End Sub

and

Private Sub Keyword_KeyUp(KeyCode As Integer, Shift As Integer)
    Debug.Print Keyword
End Sub

and

Private Sub Keyword_BeforeUpdate(Cancel As Integer)
    Debug.Print Keyword
End Sub

 

[Gasman]

theDBGuy is using the .Text property?

 

[ebs17]

If you want to identify fire as a separate word, but not as part of a word, you can use regular expressions:

WHERE RegExTest(YourField, "\bfire\b") = True

By using the \b switch, only whole words are taken into account, as well as spaces, other characters are also taken into account as separators.

RegExTest should be copied into a standard module, preferably the whole small library. Source:

MS-Office-Forum

Forum für Microsoft Office, Access, Excel, Word, Visual Basic, .NET, SQL u.v.a. - Tipps kostenlos und free Download

www.ms-office-forum.net

 

[Alc]

I just quickly tried this:

Private Sub Text0_Change()
Keyword = Me.Text0.text
MsgBox "*" & Keyword & "*"

End Sub

and got this after five keystrokes:
View attachment 107994

Thankyou!

I set a global variable using the Change, then stored the final value using the After Update event.

So simple now I know how, but it it was driving me mad.

Thanks all for the help

 

[theDBguy]

Thankyou!

I set a global variable using the Change, then stored the final value using the After Update event.

So simple now I know how, but it it was driving me mad.

Thanks all for the help

You're welcome. Glad we could assist. Good luck with your project.