Preserve trailing spaces in a textbox

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

Fred Boer

Hello:

I use data entered in a textbox to generate a SQL statement. I am aware that
Access trims the trailing spaces from the data in the textbox. Is there some
way to preserve these spaces? For example, suppose I want the search to find
instances of "Bob" but not "Bobby", and I enter "Bob " into the textbox
(note the trailing space...).

Thanks!
Fred Boer

Here is some of the code this would work with (fncQuoted deals with embedded
quotes)...

If Len(Me.txtKeyword) > 0 Then

sWHERE = " OR Author Like " & fncQuoted("*" & txtKeyword & "*") & _
" OR Title Like " & fncQuoted("*" & txtKeyword & "*") & " OR Subject
Like " _
& fncQuoted("*" & txtKeyword & "*") & " Or Series Like " &
fncQuoted("*" & _
txtKeyword & "*") & " Or ISBN Like " & fncQuoted("*" & _
txtKeyword & "*")

End If
 
Hi Fred

You would need some kind of flag to indicate if the final character was a
space.

Use the Change event of the control to examine its Text property, and set a
module-level boolean variable if the right-most character is a space. In the
AfterUpdate event of the control, append a space to its value if the flag is
true. The trailing space should stay there if assigned programmatically like
that.

A difficulty might be that if "Bob" is the last word in the field, searching
for "Bob " will not match it.
 
Hello Allen!

Thanks for the suggestion; I think I see how that would work, and will be
able to implement your solution. You are right about the issue with the
search term being at the end of a field, though... I hadn't thought of that!
Hmmm.... I suppose I could do something like: WHERE Author is LIKE "Bob " OR
Right(Author,Len(me.txtKeyword))= Me.txtKeyword...

Cheers!
Fred
 
Yes, but don't forget that there might be other characters at the end of a
word also: period, question mark, exclamation mark, dash, carriage return,
brackets, ...
 
Dear Allen:

If it's not one thing it's another... :)

I could be wrong, (Yes, really! It's happened to me before!), but I don't
think punctuation marks would come into it? I wonder if my example is
misleading... I,m not really interested in finding whole, complete words or
names, just trying to match strings. I'm trying to provide a free-form
"full-text" search capability - if the user enters "log ", the code finds
that exact string anywhere in the searched fields.

Cheers!
Fred
 
Yes, that's correct, so if that's what you want, you're home and hosed.

If you match "*log *", you match:
This is our log of attempts
but not:
This is our log.
This log, like the previous one, is not found.
What log? I don't see any log!
and so on.
 
Dear Allen:

"Hosed" ?!? What meaning does that have "down under"? :) Up here in the
"frozen North" I would say it carries the meaning of "drunk"!

Yes, I guess I am "home and hosed", but seeing your fun example suggests to
me that perhaps I *should* be concerned with punctuation after all...

All the best!
Fred
 
Dear Mr. MacRaghnaill:

Thanks! A couple of months ago, for a different project, John was kind
enough to direct me to his information about Regular Expressions. Life, work
and family have interrupted my attempt to teach myself enough about Regular
Expressions to make good use of them - but I will!

I hadn't considered them in this situation though. Ah! The joys of trying to
do something new in Access - a never-ending flowering of things to learn and
try!

Cheers!
Fred
 
Back
Top