'Like' returning different results in vba vs query builder

Mr Smin

Sometimes Excel too.
Local time
Today, 10:22
Joined
Jun 1, 2009
Messages
132
This query in the builder returns one row as expected

SELECT Log.ID, Log.Nature
FROM Log
WHERE (((Log.ID)="123") AND ((Log.Nature) Like "*Visit*"));

But this in vba returns rows where Log.Nature does not include the string 'Visit'

strSQL= "SELECT Log.ID, Log.Nature" & _
" FROM Log WHERE (((Log.ID)='" & ID & "')" & _
" AND ((Log.Nature) Like '*Visit*'))"

In fact it seems to return all rows where the ID matches. Can someone tell me what I'm doing wrong?
 
When you have issues like this, you need to find out what SQL you are actualy using. You've posted the VBA for how you want to build your statement, but you don't actually know what that statement is.

So, find out. Spit out your strSQL so you can see it with your own eyes and even put it into a query to run it.
 
Hi plog,

This is from debug.print strSQL
SELECT Log.ID, Log.Nature FROM Log WHERE (((Log.ID)='123') AND ((Log.Nature) Like '*Visit*'))

It returns all 3 rows matching 123 although only one of them has the string Visit in the Nature field. The only things I can see are swapping " to ', and the absence of ;

I am using it like this
Set rsLog = CurrentDb.OpenRecordset(strSQL)
If Not (rsLog.EOF And rsLog.BOF) Then
count = rsLog.RecordCount
End if
 
I don't know what to tell you. I recreated your issue and it didn't happen for me. Attached is the 2003 file that I created.

Q1 is this your good SQL
Q2 is your bad SQL from VBA

Both worked for me and produced only 1 result as expected.
 

Attachments

It looks like you're using DAO, but just in case the actual code uses ADO, you want % as a wildcard for ADO. * works for DAO and JET/ACE.
 
I think the issue is to do with the DAO I used in VBA, rather than just the text delimiter. Your examples were both directly in the Access query interface and they worked for me too.
 
Hi Paul,

sorry, should have posted this before
Dim rsLog As DAO.Recordset

I did try % on the offchance but that didn't work.
 
Your examples were both directly in the Access query interface and they worked for me too.

What? You just said in post #3 that it returned all 3 results. So did it work or not work for you? Or did you even try your SQL as I initially suggested?
 
Hi plog,

This is from debug.print strSQL
SELECT Log.ID, Log.Nature FROM Log WHERE (((Log.ID)='123') AND ((Log.Nature) Like '*Visit*'))

It returns all 3 rows matching 123 although only one of them has the string Visit in the Nature field. The only things I can see are swapping " to ', and the absence of ;

I am using it like this
Set rsLog = CurrentDb.OpenRecordset(strSQL)
If Not (rsLog.EOF And rsLog.BOF) Then
count = rsLog.RecordCount
End if

Swap the ' and " - it may make a difference.
Also consider removing ' ' from around your ID. Numbers don't need string conversion.
Also consider breaking it out for testing purposes ...Like "*" & "Visit" & "*"
Then try ...Like "%" & "Visit" & "%"
 
What? You just said in post #3 that it returned all 3 results. So did it work or not work for you? Or did you even try your SQL as I initially suggested?

Hi, we've been talking at cross purposes.
I opened your sample db and it contained 2 queries which together demonstrate that ' and " are equivalent, but to really recreate my problem you would need to have recreated the VBA stuff as well.
I had run my query in the query builder, and then took the SQL it generated over to VBA. I even tried pasting the debug.print output back to the query builder where it ran correctly.

I have now solved this problem as follows

rsLog.MoveFirst
rsLog.MoveLast
count = rsLog.RecordCount

It seems recordcount is not accurate without this step.
 

Users who are viewing this thread

Back
Top Bottom