Solved

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

Posted on 2013-06-12
6
439 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 42
data analyst 3 48
Search Form with Run Time Error 3075 1 22
Extract string portion 2 0
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

930 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

14 Experts available now in Live!

Get 1:1 Help Now