Why Does a Like Query in Access Not Return Any Records

Why is my LIKE query not returning any records in Microsoft Access 2013 only?

From http://technet.microsoft.com/en-us/library/cc966377.aspx :

Microsoft Jet uses partial match (or "wildcard") characters with the Like operator that are different from those used in most SQL dialects. The asterisk (*) character matches zero or more characters and is equivalent to the percent (%) character in ANSI SQL. The other Microsoft Jet partial match characters are the question mark (?), which matches any character in a single field, and the number sign (#), which matches any digit in a single field.

The query should be modified like so:

SELECT * FROM Suppliers WHERE SupplierName LIKE 's*';

Why does a LIKE query in Access not return any records?

Change your * to % as % is the wildcard search when using OLE DB.

SELECT * FROM MyTable WHERE [_Items] LIKE '%SPI%' 

SQL/query string not returning records that I know exist

Your query can be simplified as follows:

SELECT A.*, B.*
FROM A INNER JOIN b
ON A.Email = B.Email
WHERE Not (A.Email) = (B.Email);

This makes it easy to see the problem: you are joining on Email and then eliminating records based on that same field in the WHERE clause. This will always give you identically the empty set, i.e., no records.

As I understand it, you want people:

1. Who have finished some work, and
2. Who are not active on another course.

There are a few ways to do this. One was is by using outer joins:

SELECT A.*. B.*
FROM A LEFT OUTER JOIN B
ON A.Email = B.Email
WHERE B.Email is NULL

This works by doing the outer join and selecting only those people who have finished work whose entry in having completed work is empty.

You can also do this using set difference by e.g. MINUS:

SELECT A.*
FROM A
MINUS
SELECT A*
FROM A INNER JOIN B
ON A.Email = B.Email

MS-Access: HAVING clause not returning any records

You are doing in the inner select

SELECT DISTINCT PurchDate, Customer

and in the outter select

GROUP BY Customer, PurchDate

That mean all are

having count(*) = 1

I cant give you the exact sintaxis in access but you need something like this

I will use YourTable as a replacement of your inner derivated table to make it easy to read

SELECT DISTINCT Customer, PurchDate 
FROM YourTable 
WHERE Customer IN (
                    SELECT Customer
                    FROM (SELECT DISTINCT Customer, PurchDate 
                          FROM YourTable)
                    GROUP BY Customer
                    HAVING COUNT(*) > 1
                  )

• inner select will give you which customer order on more than one day.
• outside select will bring you those customer on all those days.

.

Maybe you can try something simple to get the list of customer who brought in more than one day like this

SELECT [buyer-name]
FROM RawImport
WHERE sku ALIKE "%RE%"
GROUP BY [buyer-name]
HAVING Format(MAX(purchase-date,"DD/MM/YYYY")) <> 
       Format(MIN(purchase-date,"DD/MM/YYYY"))

LIKE operator in SQL for my Access Database returns no values in ASP CLASSIC while it does if the query gets copied directly in Access

Figured it out myself. Seems that I only had to replace the * wildcards with the % wildcard. This did the trick..

access query is not returning all relevent records

You are using INNER JOINS in your query, which means only rows that exist in all joined tables are returned. So if you have a customer that purchased items in 2014 but not 2015, then their name will be excluded.

If I understand what you are attempting, you want to use LEFT or RIGHT Joins, which will return all rows from KNOXLIVE_SLCUSTM and only rows from the other tables when a match is found.

So if you do not know SQL, right-click each join line in the query designer, and select join properties.

Depending on which order the tables were initially added either the second or third radio button will be the join you want. Choose the one that selects ALL records from KNOXLIVE_SLCUSTM. Do that for all four joins and re-run your query.

Select query returning no records/results if another filed is blank/null

You need to change the logic in the conditions of the WHERE clause.

Your WHERE clause :

WHERE 
    (
        ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
        AND ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*") 
        AND ((SAP_RECORD_T.StudentID) Like "*" & [Forms]![SearchBox_F]![StudentID] & "*") 
        AND ((SAP_RECORD_T.Term) Like "*" & [Forms]![SearchBox_F]![txtTerm] & "*") 
        AND ((SAP_RECORD_T.FileComplete) Like "*" & [Forms]![SearchBox_F]![txtFileCmplt] & "*") 
        AND ((SAP_RECORD_T.CampusSubmitted) Like "*" & [Forms]![SearchBox_F]![txtCampus] & "*")
    ) OR (
        ((SAP_RECORD_T.FirstName) Is Null) 
        AND ((SAP_RECORD_T.LastName) Is Null) 
        AND ((SAP_RECORD_T.StudentID) Is Null) 
        AND ((SAP_RECORD_T.Term) Is Null) 
        AND ((SAP_RECORD_T.FileComplete) Is Null) 
        AND ((SAP_RECORD_T.CampusSubmitted) Is Null)
    )

As it is, your conditions allow records that either match on all search box conditions, or where all filter columns are NULL. Instead, you want to check NULLity for each column individually before applying the filter.

New version :

WHERE 
    (
        ( 
            ((SAP_RECORD_T.FirstName) Is Null)  
            OR ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
        )
        AND (
            ((SAP_RECORD_T.LastName) Is Null) 
            OR ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*")  
        )
        AND (
            ((SAP_RECORD_T.StudentID) Is Null)
            OR ((SAP_RECORD_T.StudentID) Like "*" & [Forms]![SearchBox_F]![StudentID] & "*")
        ) 
        AND (
            ((SAP_RECORD_T.Term) Is Null) 
            OR ((SAP_RECORD_T.Term) Like "*" & [Forms]![SearchBox_F]![txtTerm] & "*") 
        )
        AND (
            ((SAP_RECORD_T.FileComplete) Is Null) 
            OR ((SAP_RECORD_T.FileComplete) Like "*" & [Forms]![SearchBox_F]![txtFileCmplt] & "*") 
        )
        AND (
            ((SAP_RECORD_T.CampusSubmitted) Is Null)
            OR ((SAP_RECORD_T.CampusSubmitted) Like "*" & [Forms]![SearchBox_F]![txtCampus] & "*")
        )
    )

NB : these things are easier to spot when the query is properly formated (indentation, ...).

In access, query only shows results when there are records in EVERY field

Since the Employer Contact table seems to be your parent table from which the records are being selected, then you should LEFT JOIN this table to the two child tables Employer Type/Intern Functions and Internship Notes such that records from the parent table are always displayed, regardless of whether the child tables contain associated records.

For Example:

SELECT 
    [Employer Contact].Employer, 
    [Employer Type/Intern Functions].Type,  
    [Employer Type/Intern Functions].[Intern Functions/Responsibilities], 
    [Internship Notes].Date, 
    [Internship Notes].Specialist, 
    [Internship Notes].Notes
FROM 
    (
        [Employer Contact] LEFT JOIN [Internship Notes] ON 
        [Employer Contact].[Employer] = [Internship Notes].[Employer]
    ) 
    LEFT JOIN [Employer Type/Intern Functions] ON 
    [Employer Contact].[Employer] = [Employer Type/Intern Functions].[Employer]
WHERE 
    [Employer Contact].Employer = [Forms]![Employer Record Search]![cboChooseEmp] OR 
    [Forms]![Employer Record Search]![cboChooseEmp] IS NULL;

In this way, the query will only return no records if a record is not found in the Employer Contact table.

To understand more about the differences between the various join types, refer to this question.

---

Related Topics