Like Operator does not work in VBA Acc2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a strange problem with this very simple Access 2003 database. Here's
some background: The file was created in Acc2k format (I will need to share
it with people using Off2k) and it has only a few tables and a couple of
forms.

I get a data feed from an external source over which I have no control, so I
have to live wtih it "as-is". The feed has a field with a bunch on
concatenated codes that I need to process my data, so the first thing I need
to do after importing the data from text format is take some of those codes,
interpret them and put them in their own columns. Some of the codes refer to
locations, some refer to customers, and some refer to products, for instance
the field could contain "^PL=18^^CX=3421^^PR=2345^^PR=4218^", which would
mean that customer 3421 bought products 2345 and 4218 destined for location
18. The field is larger than 256 characters, so it is a memo field.

The first thing I try to do with my code is to create a column for locations
that I can use, so I have some code like this (assume Field1 has the string
from above):

Dim rstVol as ADODB.Recordset
Dim strTemp as String

(...)
strTemp = "*^PL=" & [Location nbr assigned by larger loop] & "^*"
Set rstVol = New ADODB.Recordset
rstVol.open "SELECT Field2 FROM tblImpData " & _
"WHERE (Field1 Like '" & strTemp &"')", CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
Do while not rstVol.EOF
(...) 'Assign a name to each location so I can summarize later on
Loop
rstVol.Close
(...)

The strange thing is that the "like" operator in that Select statement is
not working. I get no records returned ever. I have tried the same statements
using the user interface and they work fine, returning records, but vba never
does and does not give me an error.

I have tried everything that I can think of to fix the problem: made sure
the Jet Engine has the latest Service Pack (8.0), that all patches are
installed (MS04-014), that MDAC is up to date. I also tried different options
with the select statement itself, like changing the wildcard to %, testing
with = instead of like (which does return records when used without the
wildcards), but I can never get the Like to work.

Is this a bug in VBA or in my code? Any suggestions will be greatly
appreciated!

Fermon
 
Fermon said:
Hi, [snip]

The first thing I try to do with my code is to create a column for locations
that I can use, so I have some code like this (assume Field1 has the string
from above):

Dim rstVol as ADODB.Recordset
Dim strTemp as String

(...)
strTemp = "*^PL=" & [Location nbr assigned by larger loop] & "^*"
Click to expand...
[snip]

In ADO the wildcard character is %, not *
 
I'll be charitable, and say it's an oversight in your code, as opposed to a
bug. <g>

The wildcard character for ADO is %, not *. Change your code to

strTemp = "%^PL=" & [Location nbr assigned by larger loop] & "^%"
 
Rick and Doug,

Thanks for your prompt responses. I did try chaging the wildcard chararacter
to %, as I mentioned in the message, but it did not work. I get no returned
records either way. I will use % instead of * from now on for my queries.

Any other suggestions? I am baffled by this problem

Fermon

Rick Brandt said:
Fermon said:
Hi, [snip]

The first thing I try to do with my code is to create a column for locations
that I can use, so I have some code like this (assume Field1 has the string
from above):

Dim rstVol as ADODB.Recordset
Dim strTemp as String

(...)
strTemp = "*^PL=" & [Location nbr assigned by larger loop] & "^*"
Click to expand...
[snip]

In ADO the wildcard character is %, not *
Click to expand...
 
Check what strTemp contains to make sure it's getting assigned correctly.

Try running the query that would result using that value of strTemp to
ensure that the SQL is correct.

(...)
strTemp = "%^PL=" & [Location nbr assigned by larger loop] & "^%"
Debug.Print strTemp
strSQL = "SELECT Field2 FROM tblImpData " & _
"WHERE (Field1 Like '" & strTemp &"')"
Debug.Print strSQL
Set rstVol = New ADODB.Recordset
rstVol.open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
Do while not rstVol.EOF
(...) 'Assign a name to each location so I can summarize later on
Loop
rstVol.Close
(...)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Fermon said:
Rick and Doug,

Thanks for your prompt responses. I did try chaging the wildcard
chararacter
to %, as I mentioned in the message, but it did not work. I get no
returned
records either way. I will use % instead of * from now on for my queries.

Any other suggestions? I am baffled by this problem

Fermon

Rick Brandt said:
Fermon said:
Hi, [snip]

The first thing I try to do with my code is to create a column for
locations
that I can use, so I have some code like this (assume Field1 has the
string
from above):

Dim rstVol as ADODB.Recordset
Dim strTemp as String

(...)
strTemp = "*^PL=" & [Location nbr assigned by larger loop] & "^*"
Click to expand...
[snip]

In ADO the wildcard character is %, not *
Click to expand...
Click to expand...
 
Doug,

Thanks for looking into this for me. I tried replacing the code that assigns
the values for the actual values and I still got no returned records as long
as I use the Like operator. It is very strange, but everything seems to be
correct, yet it does not work.

Is ^ one of those special characters you have to preceed with / or \ or
double quotes?

Thanks again,

Fermon

Douglas J. Steele said:
Check what strTemp contains to make sure it's getting assigned correctly.

Try running the query that would result using that value of strTemp to
ensure that the SQL is correct.

(...)
strTemp = "%^PL=" & [Location nbr assigned by larger loop] & "^%"
Debug.Print strTemp
strSQL = "SELECT Field2 FROM tblImpData " & _
"WHERE (Field1 Like '" & strTemp &"')"
Debug.Print strSQL
Set rstVol = New ADODB.Recordset
rstVol.open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdText
Do while not rstVol.EOF
(...) 'Assign a name to each location so I can summarize later on
Loop
rstVol.Close
(...)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Fermon said:
Rick and Doug,

Thanks for your prompt responses. I did try chaging the wildcard
chararacter
to %, as I mentioned in the message, but it did not work. I get no
returned
records either way. I will use % instead of * from now on for my queries.

Any other suggestions? I am baffled by this problem

Fermon

Rick Brandt said:
Hi,
[snip]

The first thing I try to do with my code is to create a column for
locations
that I can use, so I have some code like this (assume Field1 has the
string
from above):

Dim rstVol as ADODB.Recordset
Dim strTemp as String

(...)
strTemp = "*^PL=" & [Location nbr assigned by larger loop] & "^*"
[snip]

In ADO the wildcard character is %, not *
Click to expand...
Click to expand...
Click to expand...
 
Back
Top