asked on

Building a query for multiple fields

I have multiple fields (7) in a table and I want to query all of them for the same info, then display the mane of the program.

So I have it working with one field:
strSQL = "SELECT * FROM CID WHERE Primary_Outcome1 LIKE '" & Request.Form("SearchBox") & "%'"

But I have a Primary_Outcome 1 through to 7 that I want to query as well, what's the simplest way to build the query?

Daniel Wilson

Given that table structure (denormalized, but you probably already know that):

strSQL = "SELECT * FROM CID WHERE Primary_Outcome1 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome2 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome3 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome4 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome5 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome6 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome7 LIKE '%" & Request.Form("SearchBox") & "%'"

Not pretty ... one argument for normalizing so Primary_Outcomes are in a child table.

confused_coder

ASKER

hmm this looks like it should work but it does not. Are you sure.

the code is grayed out after

strSQL = "SELECT * FROM CID WHERE Primary_Outcome1 LIKE '%" & Request.Form("SearchBox") & "%'" & OR Primary_Outcome2 LIKE

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

confused_coder

ASKER

While this does not crash with an error it is not pulling all of the content from 2 - 7 just 1 should it not be something like AND rather than OR ?

confused_coder

ASKER

never mind found the problem. thanks