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

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
SivAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
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
 
SivAuthor Commented:
You little beauty!

OK, so why can't I concoct the field "strNotes" and then put that in the where clause???
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Patrick MatthewsCommented:
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
 
SivAuthor Commented:
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
 
Patrick MatthewsCommented:
Glad to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.