Query that runs in Access but is not working in VB

[dafi]

the query looks like this:

SELECT calls_listing.dialnumber
FROM calls_listing
WHERE (Len(Trim(calls_listing!dialnumber)) In (10,11)) And ((calls_listing!dialnumber) Like ('072*'));


in Access where i wrote the query it executes fine.
in VB where i have to use it returns no rows !!!!

 

[Philly44]

I'm not completely sure but try taking out the calls_listing. from the SELECT part and take out the calls_listing! from the FROM statement. THeir aren't needed in any other environment other then Access from what I have seen.

 

[SonOfEmidec1100]

The like expression should use % instead of * as the wild card character. Also for use with dbs other than access you should/must use calls_listing.dialnumber not calls_listing!dialnumber in a query (this is not the cause of the problem).

 

[dafi]

Well i tried with % instead *, . instead ! and is still not working.
Actually on VB i use always . and not !

In fact i cannot get rid of call_listing since the where clause is part of a join-type query on 4 tables.

I suppose it can be a problem with Len and Trim functions.

Plus, there are the same problems when using left function

I couldn't find a comprehensive Ms SQL Jet Engine that lists the functions for strings .... and I think that's the important issue here

 

[xtremecode]

Bob , goddamn !
nice to find you
SonOfEmidec1100 give you the right solution for your problem.
So:
1. Use % instead of * , # or ? or other mistical signs ....
2. Make shure that the brankets are in place.(all of them)
3. There is no need to use ! or . to in your query since you have only one table.

I tested (I create a table and tested qery from vb and works fine) this and is ok:

SELECT dialnumber
FROM calls_listing
WHERE ((Len(Trim(dialnumber)) In (10,11)) And (dialnumber Like ('072%'));


Call me if is working (or not)!
[sub]xcuse my english[/sub]