Solved

VB6 and Access SQL

Posted on 2014-09-12
4
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 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