Solved

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

Posted on 2013-06-12
6
444 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

777 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