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
LVL 1
dwe0608Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Try it this way in SSMS:

USE YourDatabaseName

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


If you get results than use the same in VB6.

Incidentally SELECT * is not a good idea and you have compounded that by adding the same three fields again.  Which more than likely will make VB6 fail, so you  may be better off just writing it like this:
SELECT  *
FROM    Contacts
WHERE   Company) LIKE '%ast%'
        OR LastName LIKE '%dix%'
        OR FirstName LIKE '%jac%'
ORDER BY 
		Company;

Open in new window

Still not the best, but at least it should not trigger any errors.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwe0608Author Commented:
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
0
Anthony PerkinsCommented:
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, ...
0
Anthony PerkinsCommented:
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"?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.