Keep trailing space in field

[Guest]

I have a search form where I have multiple fields I can enter search criteria
in. I want to be able to search by the exact entry I put in the field.

For example, if I have text that contains 'US BANK', 'WILD ICHYBODS BANK AND
TRUST', and I search for 'US' I'll get both results. I want to be able to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the field, it is
removed.

How can I turn that off?

Thanks!

 

[Douglas J. Steele]

Presumably your query has something like the following in its criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with the
second one)

 

[Guest]

Doug,

I don't think it's the code...the space dissapears when I tab out of the
field. When I tab back in, the space is gone...so before the code ever runs,
there is no space in the string. It's as if Access is removing the space
automatically. You can test this on a blank db...make two unbound fields on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd one, the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried the
first (just to see if it made a difference), and the code doesn't work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the code,
it's probably not a problem with the code but how Access is treating the
field.

 

[Douglas J. Steele]

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I don't think it's the code...the space dissapears when I tab out of the
field. When I tab back in, the space is gone...so before the code ever
runs,
there is no space in the string. It's as if Access is removing the space
automatically. You can test this on a blank db...make two unbound fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried the
first (just to see if it made a difference), and the code doesn't work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the code,
it's probably not a problem with the code but how Access is treating the
field.

Presumably your query has something like the following in its criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with the
second one)

 

[Guest]

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that are too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the field at
form load or some other method?

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I don't think it's the code...the space dissapears when I tab out of the
field. When I tab back in, the space is gone...so before the code ever
runs,
there is no space in the string. It's as if Access is removing the space
automatically. You can test this on a blank db...make two unbound fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried the
first (just to see if it made a difference), and the code doesn't work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the code,
it's probably not a problem with the code but how Access is treating the
field.

Presumably your query has something like the following in its criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a search form where I have multiple fields I can enter search
criteria
in. I want to be able to search by the exact entry I put in the field.

For example, if I have text that contains 'US BANK', 'WILD ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to be able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the field, it
is
removed.

How can I turn that off?

Thanks!

 

[Douglas J. Steele]

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage of
that information. Another possibility would be to put a check box "Add space
at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the field
at
form load or some other method?

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the field,
it
is
removed.

How can I turn that off?

Thanks!

 

[Ken Snell \(MVP\)]

Or run an append / update query to insert the trailing space into the field.
Jet allows trailing spaces even though ACCESS auto-truncates. However, as
soon as the field is edited in ACCESS form, the trailing space will be lost
again....

--

Ken Snell
<MS ACCESS MVP>

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage
of that information. Another possibility would be to put a check box "Add
space at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of
the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the field
at
form load or some other method?

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code
ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the field,
it
is
removed.

How can I turn that off?

Thanks!

 

[Douglas J. Steele]

That's true, Ken, but I assumed Robert was talking about an unbound text box
he's using as a parameter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Or run an append / update query to insert the trailing space into the
field. Jet allows trailing spaces even though ACCESS auto-truncates.
However, as soon as the field is edited in ACCESS form, the trailing space
will be lost again....

--

Ken Snell
<MS ACCESS MVP>

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage
of that information. Another possibility would be to put a check box "Add
space at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that
are too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of
the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the
field at
form load or some other method?

:

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code
ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I
tried the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter
search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD
ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the
field, it
is
removed.

How can I turn that off?

Thanks!

 

[John W. Vinson]

Or run an append / update query to insert the trailing space into the field.
Jet allows trailing spaces even though ACCESS auto-truncates. However, as
soon as the field is edited in ACCESS form, the trailing space will be lost
again....

I think the problem is that they want to use an unbound textbox content as a
search criterion, not as a value to be stored in the table: i.e. to have

LIKE "US *"

as a search criterion in a parameter query. I've been cudgeling my brains and
can't think of a good way to do this (other than to monitor the textbox's
content keystroke by keystroke).

John W. Vinson [MVP]

 

[Ken Snell \(MVP\)]

Oh, sorry!

 

[Ken Snell \(MVP\)]

How about using a character other than space to "stand in for" the space
(e.g., $). Then let the query replace that $ with the space character:

< SQL statement...>
WHERE FieldName Like
IIf(Forms!FormName!TextBox Is Null, "",
Replace(Forms!FormName!TextBox, "$", " ", 1, -1, 1))
& "*";

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

Wow, that's an interesting idea...I'll try that Monday and let you know how
it works.

 

[Guest]

Doug,

Another good idea. If Ken's idea of using a 'substitute wildcard' (I'm
thinking underscore) doesn't work, I'll try the check box method.

I'll keep the thread updated and post the code that works in case anyone
else needs this type of functionality.

Thanks to all who posted ideas! Can't wait 'till Monday to try them out!

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage of
that information. Another possibility would be to put a check box "Add space
at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the field
at
form load or some other method?

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the field,
it
is
removed.

How can I turn that off?

Thanks!

 

[Douglas J. Steele]

What about John's idea of having them type the wildcard rather than you
simply including it? That's the most logical one as far as I'm concerned
(and I'm chagrined that it didn't occur to me!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Another good idea. If Ken's idea of using a 'substitute wildcard' (I'm
thinking underscore) doesn't work, I'll try the check box method.

I'll keep the thread updated and post the code that works in case anyone
else needs this type of functionality.

Thanks to all who posted ideas! Can't wait 'till Monday to try them out!

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage
of
that information. Another possibility would be to put a check box "Add
space
at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that
are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of
the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the
field
at
form load or some other method?

:

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code
ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I
tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter
search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD
ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to
be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the
field,
it
is
removed.

How can I turn that off?

Thanks!

 

[Ken Snell \(MVP\)]

What about John's idea of having them type the wildcard rather than you
simply including it? That's the most logical one as far as I'm concerned
(and I'm chagrined that it didn't occur to me!)

I concur... I often will use * as part of the entered text to allow for
"wild-card" searches.

 

[Ken Snell \(MVP\)]

A slight modification if you just want to limit the replacement to the last
character in the string:

< SQL statement...>
WHERE FieldName Like
IIf(Forms!FormName!TextBox Is Null, "",
Left(Forms!FormName!TextBox,
Len(Forms!FormName!TextBox) -1)
& Replace(Forms!FormName!TextBox, "$", " ",
Len(Forms!FormName!TextBox), -1, 1)
& "*";

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Doug,

Um...that's what I am going to do ("If Ken's idea of using a 'substitute
wildcard' (I'm thinking underscore) doesn't work, I'll try the check box
method"). The user will enter an underscore for a space if it's the last
character in the field...or I may modify it to automatically find all
underscores and replace them with spaces in case the user gets happy with
underscores.

What about John's idea of having them type the wildcard rather than you
simply including it? That's the most logical one as far as I'm concerned
(and I'm chagrined that it didn't occur to me!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Another good idea. If Ken's idea of using a 'substitute wildcard' (I'm
thinking underscore) doesn't work, I'll try the check box method.

I'll keep the thread updated and post the code that works in case anyone
else needs this type of functionality.

Thanks to all who posted ideas! Can't wait 'till Monday to try them out!

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event to
keep track of when spaces are put, and modify your code to take advantage
of
that information. Another possibility would be to put a check box "Add
space
at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

The problem is I need to search by the string 'us '. Putting a letter
behind the space will cause the user to have to search by groups that
are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all of
the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the
field
at
form load or some other method?

:

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug,

I don't think it's the code...the space dissapears when I tab out of
the
field. When I tab back in, the space is gone...so before the code
ever
runs,
there is no space in the string. It's as if Access is removing the
space
automatically. You can test this on a blank db...make two unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the 2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I
tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run the
code,
it's probably not a problem with the code but how Access is treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter
search
criteria
in. I want to be able to search by the exact entry I put in the
field.

For example, if I have text that contains 'US BANK', 'WILD
ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want to
be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a return.

When I enter the trailing space, as soon as I tab out of the
field,
it
is
removed.

How can I turn that off?

Thanks!

 

[Douglas J. Steele]

Perhaps you missesd John's post. He suggested that you have the user key in
"US *" (without the quotes)

Your code would then be

If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like "'" & _
Me.srchLegalName & """) AND "
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Um...that's what I am going to do ("If Ken's idea of using a 'substitute
wildcard' (I'm thinking underscore) doesn't work, I'll try the check box
method"). The user will enter an underscore for a space if it's the last
character in the field...or I may modify it to automatically find all
underscores and replace them with spaces in case the user gets happy with
underscores.

What about John's idea of having them type the wildcard rather than you
simply including it? That's the most logical one as far as I'm concerned
(and I'm chagrined that it didn't occur to me!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Another good idea. If Ken's idea of using a 'substitute wildcard' (I'm
thinking underscore) doesn't work, I'll try the check box method.

I'll keep the thread updated and post the code that works in case
anyone
else needs this type of functionality.

Thanks to all who posted ideas! Can't wait 'till Monday to try them
out!

:

There's no way to turn it off that I'm aware of.

What you could possibly do is put code into the field's KeyPress event
to
keep track of when spaces are put, and modify your code to take
advantage
of
that information. Another possibility would be to put a check box "Add
space
at end?" after the text box, and have your users check that when
appropriate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug,

The problem is I need to search by the string 'us '. Putting a
letter
behind the space will cause the user to have to search by groups
that
are
too
small.

For example, 'us b' then 'us f' then 'us g' when we really want all
of
the
'us ' series.

Is there no way to 'turn off' the truncation by either prepping the
field
at
form load or some other method?

:

That's correct: Access doesn't allow trailing spaces.

I wasn't suggesting putting spaces anywhere in your code with my
first
suggestion. I was saying to type a character after the space.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug,

I don't think it's the code...the space dissapears when I tab out
of
the
field. When I tab back in, the space is gone...so before the
code
ever
runs,
there is no space in the string. It's as if Access is removing
the
space
automatically. You can test this on a blank db...make two
unbound
fields
on
a form, both text. Enter 'ab ' in the first one and tab to the
2nd
one,
the
space will dissapear.

Since the 2nd option would return the same problem I have now, I
tried
the
first (just to see if it made a difference), and the code doesn't
work -
generates a type mismatch error.

The code starts like this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like ""*" &
Me.srchLegalName & "*"") AND "
End If

When I try to modify it like you suggested, it changes to this:
If Not IsNull(Me.srchLegalName) Then
strWhere = strWhere & "([LenderLegalName] Like " * " &
Me.srchLegalName & " * ") AND "
End If

But like I said...if the space is stripped before I can even run
the
code,
it's probably not a problem with the code but how Access is
treating
the
field.

:

Presumably your query has something like the following in its
criteria:

Like "*" & Forms!FormName!ControlName & "*"

Either type "US B" into the text box, or change the criteria to

Like Forms!FormName!ControlName & "*"

(although you'd still get USELESS PLACE TO SAVE MONEY returned
with
the
second one)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a search form where I have multiple fields I can enter
search
criteria
in. I want to be able to search by the exact entry I put in
the
field.

For example, if I have text that contains 'US BANK', 'WILD
ICHYBODS
BANK
AND
TRUST', and I search for 'US' I'll get both results. I want
to
be
able
to
enter 'US ' (US and a space) and only get 'US BANK' as a
return.

When I enter the trailing space, as soon as I tab out of the
field,
it
is
removed.

How can I turn that off?

Thanks!