DBquery not accepting condition like in Access

[Semper]

Hi,

is this normal behaviour, it seems whenever i put the condition like in the DBQuery SQL statement i end up with 0 results.

[JRL]

I've got a half dozen scripts that have dbQuery using "like" so I'm certain there is nothing wrong with using "like". Are you sure about your connection string? Do you have and example of an SQL line that works and an example using "like" that fails?

[EnderFFX]

Hi,

is this normal behaviour, it seems whenever i put the condition like in the DBQuery SQL statement i end up with 0 results.

I've done about a dozen or so, every time I've come across a 0 result it is because of a mistake in my Query.

Give us the Query and let us take a look!

[Semper]

The thing is, i'm not writing the SQL statements in DBQuery, i have queries in access which i pass to DBQuery. That worked good for months.

Until I changed something in those querys after which i got 0 results.
And when i run that same query in access, i get the results.

After digging i realised i added the LIKE '*something*' (with wildcards)
So my concerne is a query working in access but not in MS.

[Marcus Tettmar]

SQL usually wants % for wildcards not *.

Try % instead.

Remember that outside of Access the SQL syntax depends on the ODBC/ADO driver you are using. Not everything that works inside of Access may be supported by the connection driver you are using.

Like clauses and wildcards are absolutely supported and we use them all the time ... But the syntax is entirely dependent on the ODBC/ADO drivers you are using defined by the connection string. Macro scheduler simply passes the query to the driver. Macro Scheduler does NO processing or checks with the SQL - it doesn't know anything about it - it simply passes it on to the driver and database you have specified. So really, you need to look at the docs for the database/driver you are using.

[Semper]

I thought as much, thanks.

I finally replaced the * with % wildcard in Access query.
Now when i run it in Access it shows up 0 results because Access don't deal with %, but passing that same query in MS shows the records correctly and that is what i need.

Thanks again.

[EnderFFX]

I thought as much, thanks.

I finally replaced the * with % wildcard in Access query.
Now when i run it in Access it shows up 0 results because Access don't deal with %, but passing that same query in MS shows the records correctly and that is what i need.

Thanks again.

Please post the code, and post the table structure/fields. This is my area of expertise and I may be able to help.

[armsys]

Please post the code, and post the table structure/fields. This is my area of expertise and I may be able to help.

Yeah, Semper, please post your (partial) script here so we all can learn from you and EnderFX, please. Thanks.

[Marcus Tettmar]

Looks like the issue is solved so not sure why you want to see table structure.

Access has its own syntax INSIDE Access which is not necessarily standard SQL. E.g. it can use * as wildcard. But the standard for wildcards in SQL is % and it would appear that the JET/ODBC/ADO driver (whatever is being specified in Macro Scheduler for the DB connection) wants %.

But inside Access, Access wants *.

So in Access stick with the * but when you port your SQL into Macro Scheduler use % (Unless you know of a connection driver which can handle the * but in my experience all connection drivers want %).

Actually a Google search reveals that some versions of the Access ODBC driver can handle *. See:

http://kbalertz.com/234525/Running-Stor ... sults.aspx

So, as I said originally, the behaviour is determined by the connection driver you are using. There's a difference between what Access can use *internally* and what syntax is supported by the connection driver you are asking Macro Scheduler to use. And as shown in the above page there can be differences between one driver and another.

Remember that Macro Scheduler works BLIND. It cares not about the SQL you give it and doesn't understand it - it is just a messenger and simply passes it on to the connection driver.

Complain to Microsoft about the inconsistency not us.

[EnderFFX]

Looks like the issue is solved so not sure why you want to see table structure.

I can't tell you how many times I've been debugging SQL queries for hours when it turned out a table field was given a type that was inconsistent with the query.

For some reason i read the very last message as the problem was not still open... I reread it today and realized he had the solution.

When posting SQL questions it is important to post:
the SQL query
the error message
the table structure

[armsys]

When posting SQL questions it is important to post:
the SQL query
the error message
the table structure

Thanks EnderFFX.