docmd.execute(sql) and query sql return different results

[mrlw00r]

Hi,

Got a bit of a strange one here. When I use docmd.execute I get a different result to when I put the exact same sql into a query:

SELECT Count([moduleID]) as [noRecords] FROM [Module] WHERE (

 like "03*");

When run in a query I get returned the number of module records where code begins with 03, but when run through docmd I get zero. I also use the same where expression in filters and it works fine. If I change the where expression so that it is a like but on a different field then exactly the same happens. 

Does anyone have any ideas? I've had a search for similar problems, but turned up nothing!

tia,
Mike

 

[dcx693]

Do you mean Currentdb.Execute? I don't see an execute method for DoCmd. If so, you cannot execute a select query. Maybe that's why you're geting zero.

 

[mrlw00r]

Oh... yes!! I do of course mean an ADODB.Connection object, not docmd!
It seems like the problem happens only when a like expression with a wildcard in it is used...

 

[SikhSioux]

Posting a new thread

I am new to this forum. Does anyone know how i can post a new thread?

 

[dcx693]

Why do you even want to use the Execute method to open the select query though?

 

[mrlw00r]

What?!
Look next to the post reply button!!!!
Blimey...
(sorry... been a long day...)

 

[mrlw00r]

I use the execute method by default just because that's how I started programming in Access and I'm used to it!
I don't need to use the query - I was just copying the SQL into it to see if it worked (which it did) because I new that I should be getting back a different result from execute.