Preserve trailing spaces in a textbox

[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

 

[Allen Browne]

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.

 

[Fred Boer]

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

 

[Allen Browne]

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, ...

 

[Fred Boer]

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

 

[Allen Browne]

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.

 

[Fred Boer]

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

 

[Brendan Reynolds]

Hi Fred,

John Nurick has some examples of using Regular Expressions in VBA at the
following URL, which I think you may find useful ...

http://www.j.nurick.dial.pipex.com/Code/index.htm

 

[Fred Boer]

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