Solved

VB6 and Access SQL

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What's wrong with this T-SQL Foreign Key? 7 46
SQL - Update field defined as Text 6 17
SetFocus doesn't wait for input 14 34
SQL Query assistance 16 27
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.

809 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