Solved

VB6 and Access SQL

Posted on 2014-09-12
4
331 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

7 Experts available now in Live!

Get 1:1 Help Now