SQL strings and Access Queries

[hirenJ]

Hi all, thanks for all your help over the last month - hate to say that im still stuck with this SQL string...

I have a string variable called RespCode that I need to use in a SQL string,
_________________________________
SELECT Contact_Table.RESPCODE
FROM Contact_Table
WHERE (((Contact_Table.RESPCODE) Like '*" & strRespCode & "*' ));"
_________________________________

as you can see, in the string i need to concanate strRespCode with two * delimiters -- i.e the Like clause becomes *002* (where 002 is RespCode)

When I execute the query I always get a null recordset!! Ive tried using single quotes, and double quotes around the Like Clause -- still no luck.

what puzzles me is that when I paste the runTime SQL string into an access query -- it works fine??

What is the query not executing when built dynamically??



Thanks

Hj

)

 

[MisterC]

What is the data type of your RESPCODE field? Can you provide some sample data in the field?

 

[hirenJ]

RESPCODE is text data type ...

its a four digit number i.e. 0002

however it has to be held as a text data type, as a query on
*2* would retrieve different results to *0002*

Hj

)

 

[MisterC]

Sounds like your query should work fine:

SELECT Contact_Table.RESPCODE FROM Contact_Table 
WHERE  Contact_Table.RESPCODE Like '*" & strRespCode & "*';"

Are you sure strRespCode contains an appropriate string?
Maybe you should

Debug.Print strRespCode

to make sure.

 

[jebry]

Hi!

The only other thing I can think of is to make sure that you actually have spaces where you need them (like between RESPCODE and From and between table and Where). Sometimes, when you build an SQL string in code, spaces are not added everywhere they are needed and the code won't work.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[MisterC]

jebry:
Excellent point! As a matter of fact, when i run into SQL problems I usually:

Dim strSQL    as String
strSQL = "SELECT Contact_Table.RESPCODE " _
         "FROM Contact_Table " _
         "WHERE  Contact_Table.RESPCODE Like " _
         "'*" & strRespCode & "*';"
Debug.Print strSQL

...to make sure my sql looks ok.

 

[hirenJ]

Thanks for all your help guys!!

The answer to all the above is yes...

When i print strRespCode in the immediate window it appears
as a string, with the preceding zeros,

When i print the SQL string in the immediate window it looks perfectly fine -- even stranger -- when I then paste this SQL statement into the access query builder, the query runs fine?!!

If the SQL statement wasnt write, surely it wouldnt run when pasted into the query designer?

Hj

P

 

[MisterC]

What code are you using to run the query? Could you post it?

 

[hirenJ]

Here you go folks....

the SQL string when printed in the debug window looks fine -- i still cant imagine whats wrong with this?



________________________________________________________
Public Function Get_Contacts2(strRespCODE As String)
'GET number of contacts with selected respCode

Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim quo As String

'database connection
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'generate SQL string
strSQL =
" SELECT Contact_Table.RESPCODE" & _
" FROM Contact_Table" & _
" WHERE (((Contact_Table.RESPCODE)" & _
" Like '*" & strRespCODE & "*' ));"

Debug.Print strSQL


rst.Open strSQL, conn, adOpenStatic, adLockReadOnly, adCmdText

Do Until rst.EOF
Debug.Print rst![RespCode]
rst.MoveNext
Loop
Beep

'get recordcount
If (rst.EOF = True And rst.BOF = True) Then
MsgBox ("Houston we have a problem&quot
Else
End If
Beep

Set rst = Nothing
Set conn = Nothing
End Function

____________________________________________________

 

[iajacob]

Hi,

If you use a % sign instead of the * asterisk, I think it should work. Hope this helps.

 

[iajacob]

Hi again,

For some reason recordsets do not like the asterisk when using "LIKE" in an SQL statement. I had the same problem.

If you use " Like '%" & strRespCODE & "*' ));"
I think it should work. Please see web article below:

http://www.cdmcpcs.com/Developers/adolike.shtml

 

[cmmrfrds]

How do you know that no records are being returned? There was a bug in Access 97 where you need to do a movelast after returning the recordset before the EOF, BOF, and Count where set properly. Access 2000 is okay.

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly, adCmdText

Debug.Print rst.RecordCount
Debug.Print rst.BOF
Debug.Print rst.EOF

Do Until rst.EOF
Debug.Print rst![RespCode]
rst.MoveNext
Loop
Beep

 

[hirenJ]

thanks for all of your help!!! From what I can make out, the problem lies with using ADO in conjunction with the wildcard characters (i.e. * ) ...

After re-coding the module using DAO...the code ran fine!!

Is this a bug/flaw with the ADO 2.5 model?


Hja

 

[cmmrfrds]

Check this link out.

http://www.devguru.com/features/knowledge_base/A100219.html

When using ADO the wild card is %
When using DAO the wild card is *