asked on

VB6 and Access SQL

Hi Guys

Using vb6 and Access 2007 database ...

I am trying to run a query on a table querying multiple field values as follows:

SELECT Contacts.*, Contacts.Company, Contacts.LastName, Contacts.FirstName
FROM Contacts
WHERE (((Contacts.Company) Like "%ast%")) OR (((Contacts.LastName) Like "%dix%")) OR (((Contacts.FirstName) Like "%jac%"))
ORDER BY Contacts.Company;

Open in new window

despite there being data which matches the criteria, no results are being yielded ...

Can someone point me in the right direction ..

MTIA

DWE

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwe0608

ASKER

Hi Anthony

Thank you for the assistance

The following sql string now works

SELECT * From Contacts WHERE ((([Company]) Like '%kj%')) OR ((([LastName]) Like '%kj%')) OR ((([FirstName]) Like '%kj%')) ORDER BY [Company];

Open in new window

One of the issues I came across is that I test my sql string inside access and the wildcard "%" is actually "*" in that context - but when in VB, the wildcard prevails as "%".

The actual code is formatted as follows:

    t = "kj"
    
    sql = "SELECT * From Contacts "
    sql = sql & "WHERE ((([Company]) Like '%" & t & "%')) "
    sql = sql & "OR ((([LastName]) Like '%" & t & "%')) "
    sql = sql & "OR ((([FirstName]) Like '%" & t & "%')) "
    sql = sql & "ORDER BY [Company]; "

Open in new window

I appreciate the assistance

Regards

DWE

Anthony Perkins

One of the issues I came across is that I test my sql string inside access and the wildcard "%" is actually "*" in that context - but when in VB, the wildcard prevails as "%".
That is not correct. If you are using JET the wildcard is *. If on the other hand you are using SQL Server then the wildcard is %. In other words, if you are using VB6 against SQL Server than the wildcard is %. So what provider are you using?

Lose the parenthesis and square brackets ([]) and write your code this way:

SELECT  *
FROM    Contacts
WHERE   Company LIKE '%kj%'
        OR LastName LIKE '%kj%'
        OR FirstName LIKE '%kj%'
ORDER BY Company;

Open in new window

Then consider losing SELECT * entirely and replace with naming the columns explicitly as in SELECT Col1, Col2, ...

Anthony Perkins

What could I have done more to merit an "A" grade? Was there something I said or did not say that caused you to grade my solution with a "B"?