help with sql logic and syntax,,,,, 1

[Extras]

am stumbling over the synatx-logic of this simple sql statment.

I have an Access database with the following two text fields...
SiteSumm_Area1
SiteSumm_Area2

How do I craft an SQL query that will deliver for me a set of records in which the above fields for a particular record do not match a particular string...

I have tried the following two codes with no luck.
The first does not deliver the complete set of records and the second delivers a syntax error...

I look forward to your guidance.

Code Try #1
<CFQUERY NAME=&quot;SortNoMatch_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE (Summ_Area1 <> '#url.SortKey#') AND (Summ_Area2 <> '#url.SortKey#')
</CFQUERY>


Code Try #2
<CFQUERY NAME=&quot;SortNoMatch_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE (Summ_Area1 AND Summ_Area2) <> '#url.SortKey#'
</CFQUERY>

 

[ecobb]

Try this:

<CFQUERY NAME=&quot;SortNoMatch_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE (Summ_Area1 <> '#url.SortKey#' AND Summ_Area2 <> '#url.SortKey#')
</CFQUERY>

If it works, I'll tell you why. If it doesn't, then I goofed again...

 

[Extras]

It did not work...maybe it would help if I gave some backgrond.

First - I plan to display a set of records where the records match Area1

<CFQUERY NAME=&quot;Sort1_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE Summ_Area1 = '#url.SortKey#'
</CFQUERY>

Second, I plan to display a set of records where the records match Area2

<CFQUERY NAME=&quot;Sort2_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE Summ_Area2 = '#url.SortKey#'
</CFQUERY>

the both of these work. Now, I wanted to display the remaining records that do not match either...

I tried your code also but got the same result. It only shows some of the remaining records. I am using a recordcount output next to each query output to see if the totals match up to total set of records...

Cannot seem to figure where the query might be going wrong..

 

[erwino]

Looking at the code I assume that the fields that you are matching are characters (String). When looking at non-matches you would use != in the query, not <>
Try that

<CFQUERY NAME=&quot;SortNoMatch_list&quot; DATASOURCE=&quot;#request.SiteDSN#&quot;>
SELECT *
FROM SummaryList
WHERE (Summ_Area1 != '#url.SortKey#' AND Summ_Area2 != '#url.SortKey#')
</CFQUERY>

(Could also use NOT LIKE '%#url.SortKey#%')

Erwin Oosterhoorn
Analyst Programmer,
Roller hockey'er, biker,
ice hockey player/fan.

 

[r937]

>> I am using a recordcount output next to each query output to see if the totals match up to total set of records...

i betcha you have some nulls in there

nulls are not going to be returned by

where Summ_Area1 = '#url.SortKey#'

nulls are also not going to be returned by

where Summ_Area1 <> '#url.SortKey#'

so if the columns contain nulls, to make the counts come out, your &quot;not&quot; query should look like this:

where ( Summ_Area1 is null
or Summ_Area1 <> '#url.SortKey#' )
and ( Summ_Area2 is null
or Summ_Area2 <> '#url.SortKey#' )

rudy

http://r937.com/

 

[Extras]

You are correct Nulls were an issue. Another friend pointed that to me.

Thanks for all your help!