where clause for vbcrlf 2

[edpatterson]

I am attempting to query records out of a database which have a crlf in one of the fields. I am using ado and vbscript.

The string is in the form of:

"192.169.1.2&vbcrlf&192.168.1.3&vbcrlf&192.168.1.4"

It is actually the IP_ADDRESS column as saved by BGInfo when selecting a Microsoft Access database.

Tried
[tt]
select ip_address from bginfotable where ipaddress = '%vbcrlf%'
select ip_address from bginfotable where ipaddress = '*vbcrlf*'
select ip_address from bginfotable where ipaddress = '%chr(10)%'
select ip_address from bginfotable where ipaddress = '*chr(10)*'
[/tt]

As you can probably see, I am fairy new to this.

Thanks.

 

[Crowley16]

erm, so the string actually contains the literal characters of &vbcrlf or line breaks?

if the former, then search for '%vbcrlf%', if the latter, search for '%' & chr(10) & chr(13) & '%'

p.s. line breaks can be either chr(10), chr(13) or both, so you may have to test for each individually...

--------------------
Procrastinate Now!

 

[Remou]

Try '%" & (Chr(13) + Chr(10)) & "%'

 

[GHolden]

If you are searching with-in the field the SQL you need to use LIKE not =

SELECT IP_ADDRESS FROM BGINFOTABLE WHERE IP_ADDRESS LIKE "*vbcrlf*"

If you are using VB and ADO you may need to change the * to % and you may need single quotes around the *vbcrlf* (as below), but the Jet SQL is as above.

strSQL = "SELECT IP_ADDRESS FROM BGINFOTABLE WHERE IP_ADDRESS LIKE '%vbcrlf%'"

What Crowley posted still holds true regarding how the vbcrlf is actually stored.


There are two ways to write error-free programs; only the third one works.

 

[edpatterson]

Thanks to all. I am using
[tt]
...'%" & (Chr(13) + Chr(10)) & "%'...
[/tt]
and it is working like a champ.

 

[PHV]

If the WHERE clause is built in VBA:
...'%" & vbCrLf & "%'...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886