Problem with LIKE operator

  • Thread starter Thread starter James Houston
  • Start date Start date
J

James Houston

I need to run a query where I know the first 4 and last 4 characters in a 16
character string. But the following query

SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE (((Trans.Card) Like [Begin] & "*" & [End]));

returns no results. If I provide a value for the [Begin] parameter and
leave the [End] parameter blank, I get several hits. If I provide values
for both parameters, I get nothing. Likewise, if I leave the [Begin]
parameter blank & provide a value for the [End] parameter, I get no results.
I really need to be able to match both ends of the string. Anybody have any
ideas? Thanks in advance.

Jim
 
SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE Trans.Card LIKE [Begin] & "*"
AND Trans.Card LIKE "*" & [End];
 
Try
SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE Trim(Trans.Card) Like [Begin] & "*" & [End]

I'm guessing that you have trailing spaces in the field

Another method that should work even with spaces or other unknown characters
at the end of the string would be to test for

Like [Begin] & "????????" & [End] & "*"

Where each ? stand for any single character.
 
Hi,


Should work unless the parameter is really a blank space, and not null or a
zero length string. Try TRIM( ) around each parameter:

LIKE TRIM([begin]) & "*" & TRIM([end])


Hoping it may help,
Vanderghast, Access MVP
 
Hi James,

There's nothing obviously wrong with the query; I've just tested the
same syntax in a test database:

SELECT * FROM FAQ WHERE fSubject LIKE [BEGIN] & '*' & [END];

So my first thought is that the Card field doesn't end the way you think
it does. Could there be a space, a newline, or other non-obvious
characters? That would give the results you're getting.


I need to run a query where I know the first 4 and last 4 characters in a 16
character string. But the following query

SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE (((Trans.Card) Like [Begin] & "*" & [End]));

returns no results. If I provide a value for the [Begin] parameter and
leave the [End] parameter blank, I get several hits. If I provide values
for both parameters, I get nothing. Likewise, if I leave the [Begin]
parameter blank & provide a value for the [End] parameter, I get no results.
I really need to be able to match both ends of the string. Anybody have any
ideas? Thanks in advance.

Jim
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'll add my 2 cents....

If the user doesn't enter anything in the parameter prompts then the
parameter is a NULL. Therefore, since that would concatenate a NULL to
a string, the result of that concat is NULL. So, do this:

WHERE Trans.Card Like Nz([Begin],"") & "*" & Nz([End],"")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9lHfIechKqOuFEgEQJO9QCfQMza4GwadI5YY5WpSe5+3mHVxpAAn1be
HUXb2hdNtKYICjNkljR7KKpr
=Js0G
-----END PGP SIGNATURE-----
 
Actually when you use the & concatenate operator, you end up with
Like "*"
If you concatenate using the + then you are correct the null propagates.

Of course, that applies to Access.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'll add my 2 cents....

If the user doesn't enter anything in the parameter prompts then the
parameter is a NULL. Therefore, since that would concatenate a NULL to
a string, the result of that concat is NULL. So, do this:

WHERE Trans.Card Like Nz([Begin],"") & "*" & Nz([End],"")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9lHfIechKqOuFEgEQJO9QCfQMza4GwadI5YY5WpSe5+3mHVxpAAn1be
HUXb2hdNtKYICjNkljR7KKpr
=Js0G
-----END PGP SIGNATURE-----

James said:
I need to run a query where I know the first 4 and last 4 characters in a
16 character string. But the following query

SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE (((Trans.Card) Like [Begin] & "*" & [End]));

returns no results. If I provide a value for the [Begin] parameter and
leave the [End] parameter blank, I get several hits. If I provide values
for both parameters, I get nothing. Likewise, if I leave the [Begin]
parameter blank & provide a value for the [End] parameter, I get no
results. I really need to be able to match both ends of the string.
Anybody have any ideas? Thanks in advance.
Click to expand...
Click to expand...
 
Thanks to all who offered advice. Turns out MGFoster was right. I had
foolishly assumed that since the string I was searching for is always 16
characters long, the field in the table would be the same length. Turns out
it isn't, its 20 characters long. Adding an asterik to the end of the
string solved the problem.
 
Hi,


That is not the same thing. If you add a * after [ending], then the [ending]
may just be NOT the end !


"ABCDEF" LIKE "A*C*"

would return true, even if "C" is NOT at the end of the value in the left
of the operator LIKE.


If you use Jet, the string is always a 'varchar' and the exact size of the
declared field DOES NOT MATTER, it is just the maximum number of characters
that the field can hold. On the other hand, if your data comes from another
source that has "padded" the field with blanks, then use TRIM


TRIM(yourFieldName) LIKE TRIM( [starting] ) & "*" & TRIM( [ending] )


which will remove any spaces used for padding and thus, if the record is
kept, [ending] will really "ends" what is stored in your field, spaces
removed.



Vanderghast, Access MVP



James Houston said:
Thanks to all who offered advice. Turns out MGFoster was right. I had
foolishly assumed that since the string I was searching for is always 16
characters long, the field in the table would be the same length. Turns
out it isn't, its 20 characters long. Adding an asterik to the end of the
string solved the problem.


James Houston said:
I need to run a query where I know the first 4 and last 4 characters in a
16 character string. But the following query

SELECT Trans.Number, Trans.Ticket, Trans.Date, Trans.Card
FROM Trans
WHERE (((Trans.Card) Like [Begin] & "*" & [End]));

returns no results. If I provide a value for the [Begin] parameter and
leave the [End] parameter blank, I get several hits. If I provide values
for both parameters, I get nothing. Likewise, if I leave the [Begin]
parameter blank & provide a value for the [End] parameter, I get no
results. I really need to be able to match both ends of the string.
Anybody have any ideas? Thanks in advance.

Jim
Click to expand...
Click to expand...
 
Back
Top