Solved

MS Access SQL Query to get records where a certain string is within a field

Posted on 2013-06-12
6
454 Views
Last Modified: 2013-06-12
Hi,
I am trying to construct an MS Access 2010 query that returns me some rows where the word "knotweed" occurs in concatenation of a number of Note Fields, this was my first idea:

SELECT Invoices.[Invoice Number], Invoices.fkCustomerID, Invoices.InvTotal,
Invoices.[Note 1] + Invoices.[Note 2] + Invoices.[Note 3] + Invoices.[Note 4] AS StrNotes
FROM Invoices
WHERE InStr(1,[StrNotes],"knotweed",1)>0;

This doesn't work as Access keeps throwing up an input box asking for the parameter StrNotes. So then I tried this:

SELECT Invoices.[Invoice Number], Invoices.fkCustomerID, Invoices.InvTotal,
Invoices.[Note 1] + Invoices.[Note 2] + Invoices.[Note 3] + Invoices.[Note 4] AS StrNotes, InStr(1,[StrNotes],"knotweed",1) AS FoundKnotWeed
FROM Invoices
WHERE [FoundKnotWeed] >0;

And still it thinks I am asking for a parameter called "FoundKnotWeed".

Can anyone tell me where I am going wrong??

Siv
0
Comment
Question by:Siv
[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
  • 4
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39242672
SELECT Invoices.[Invoice Number], Invoices.fkCustomerID, Invoices.InvTotal,
Invoices.[Note 1] + Invoices.[Note 2] + Invoices.[Note 3] + Invoices.[Note 4] AS StrNotes
FROM Invoices
WHERE InStr(1,Invoices.[Note 1] + Invoices.[Note 2] + Invoices.[Note 3] + Invoices.[Note 4],"knotweed",1)>0;
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39242674
Or:

SELECT Invoices.[Invoice Number], Invoices.fkCustomerID, Invoices.InvTotal,
Invoices.[Note 1] + Invoices.[Note 2] + Invoices.[Note 3] + Invoices.[Note 4] AS StrNotes
FROM Invoices
WHERE Invoices.[Note 1] Like "*knotweed*" Or
    Invoices.[Note 2] Like "*knotweed*" Or
    Invoices.[Note 3] Like "*knotweed*" Or
    Invoices.[Note 4] Like "*knotweed*"
0
 

Author Comment

by:Siv
ID: 39242684
You little beauty!

OK, so why can't I concoct the field "strNotes" and then put that in the where clause???
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39242695
It has to do with the order in which Jet is processing the commands.  The WHERE clause criteria are evaluated before expressions from the SELECT clause are available.
0
 

Author Closing Comment

by:Siv
ID: 39242706
Patrick,

Thanks, that helps me understand going forward.
I just couldn't understand why it kept thinking I was putting in a parameter.
Thanks for your help.

Siv
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39242710
Glad to help :)
0

Featured Post

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Suggested Courses

617 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