Solved

VB6 and Access SQL

Posted on 2014-09-12
4
333 Views
Last Modified: 2014-09-12
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
0
Comment
Question by:dwe0608
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40320347
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
 
LVL 1

Author Comment

by:dwe0608
ID: 40320379
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40320396
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40320398
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now