Filtering Data using AND NOT

[tech69]

Hi all

Got some code running on an alarm DB, things have been filtering through to an area it shouldnt have... to correct this problem the DB designer used AND NOT statements... I have tried to replicate this but failed. Could someone point me in the right direction please.

highlighted my bit in red....

Function CAT()
Set db = CurrentDb
strsql = "SELECT [qry_alm].ID_NO, [qry_alm].TIME_STAMP, [qry_alm].SRC_NODE, [qry_alm].POINT_NAME, [qry_alm].ALARM_TYPE, [qry_alm].ALARM_PRIO, [qry_alm].POINT_DSCR, [qry_alm].UNIT_ID " _
       & "FROM [qry_alm] " _
       & "WHERE " & strDate & " AND " _
       & "([qry_alm].UNIT_ID ='FC' Or [qry_alm].UNIT_ID ='FF' Or [qry_alm].UNIT_ID ='FL' Or [qry_alm].UNIT_ID ='M3' " _
       & "Or [qry_alm].UNIT_ID ='AS' AND ([qry_alm].ALARM_PRIO Not Like 'LOW*'));" _
    [COLOR=red]   & "AND NOT ((qry_pPOS_rpt.POINT_NAME) = 'FCU2165' )" _[/COLOR]
 

       'AND [qry_alm].ALARM_PRIO = 'JOURNAL'
If status = "target" Then
    CurrentDb.QueryDefs("qry_target").sql = strsql
ElseIf status = "period" Then
    CurrentDb.QueryDefs("qry_period").sql = strsql
ElseIf status = "report" Then
    CurrentDb.QueryDefs("qry_rpt").sql = strsql
    Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
        With rst
            If rst.RecordCount <= 0 Then
               MsgBox ("There have been no alarms in " & combo_val & " for the selected time period.")
               DoCmd.Close acForm, "frm_progress"
               quit = True
               Exit Function
            End If
        End With
    rst.Close
End If
End Function

Error message:
Run Time Error: 3142
Characters found after end of SQL statement.

Any help would be much appreciated
THANKS!

 

[trichter]

maybe try & "AND ((qry_pPOS_rpt.POINT_NAME) <> 'FCU2165' )" _

 

[MSAccessRookie]

The line before has a ";" character which will terminate an sql statement

& "Or [qry_alm].UNIT_ID ='AS' AND ([qry_alm].ALARM_PRIO Not Like 'LOW*'));" _
& "AND NOT ((qry_pPOS_rpt.POINT_NAME) = 'FCU2165' )" _

-------------------------------------------------------------------

Are those blank lines between the line

& "AND NOT ((qry_pPOS_rpt.POINT_NAME) = 'FCU2165' )" _

and the line

'AND [qry_alm].ALARM_PRIO = 'JOURNAL'

actually in the code? I think they may also need to be removed because the concatenations need to all be in sequential lines

In addition, you might want to consider using IN instead of the joined OR statements. Something like this perhaps:

(([qry_alm].UNIT_ID IN ('FC', 'FF', 'FL', 'M3', 'AS')) AND ([qry_alm].ALARM_PRIO Not Like 'LOW*'));"

 

[tech69]

OK so i've removed those extra lines, and moved the ';' to the end of the last statement. looks like this:

strsql = "SELECT [qry_alm].ID_NO, [qry_alm].TIME_STAMP, [qry_alm].SRC_NODE, [qry_alm].POINT_NAME, [qry_alm].ALARM_TYPE, [qry_alm].ALARM_PRIO, [qry_alm].POINT_DSCR, [qry_alm].UNIT_ID " _
       & "FROM [qry_alm] " _
       & "WHERE " & strDate & " AND " _
       & "([qry_alm].UNIT_ID ='FC' Or [qry_alm].UNIT_ID ='FF' Or [qry_alm].UNIT_ID ='FL' Or [qry_alm].UNIT_ID ='M3' " _
       & "Or [qry_alm].UNIT_ID ='AS' AND ([qry_alm].ALARM_PRIO Not Like 'LOW*'))" _
       & "AND NOT ((qry_pPOS_rpt.POINT_NAME) = 'FCU2165' );" _

Now getting error:
Run time error 3061
Too Few parameters. Expected 1.


Thanks again all... im useless - literally

 

[georgedwilkinson]

No one is useless.

What's strDate? It's sitting out there all by itself in the where clause.

If fixing that doesn't help, add this line after the line you just posted:

Debug.Print strsql

and copy the value in the immediate window over here so we can take a look at it.

 

[dapfeifer]

I had a similar problem with the too few parameters deal and believe the issue was related to my WHERE clause. Make sure everything in there contains a value because if it doesn't I think access doesn't consider it an actual WHERE "parameter", hence the too few parameters error.

 

[MSAccessRookie]

No one is useless.

What's strDate? It's sitting out there all by itself in the where clause.

If fixing that doesn't help, add this line after the line you just posted:

Debug.Print strsql

and copy the value in the immediate window over here so we can take a look at it.

I overlooked this field because I assumed that we were looking at only a small piece of a VBA program, and that the definition of strdate was taken care of there. As Georgewilkinson points out above, the definition of strdate will affect whether strsql can be built right. As written, I think it must be string and needs to be converted otherwise. The VBA experts should correct me on this if I am incorrect.

A few things that I noticed about the code were

1. There were "&" characters that I do not think belonged in the beginnings of some of the lines, so I removed them. (LOOKS LIKE I WAS WRONG ABOUT THIS ONE)
2. Some of the text of the SQL Statement was unnecesarily split, so I joined them.
3. There was a space missing between the ")) and the "AND NOT" on the last line.

The last item was difficult to see until I combined the lines to determine the interpretation of the string strsql.

I have provided an updated copy of the code below.

[FONT=Courier New]strsql = "SELECT [qry_alm].ID_NO, [qry_alm].TIME_STAMP, " & _[/FONT]
[FONT=Courier New]"[qry_alm].SRC_NODE, [qry_alm].POINT_NAME, [qry_alm].ALARM_TYPE, " & _[/FONT]
[FONT=Courier New]"[qry_alm].ALARM_PRIO, [qry_alm].POINT_DSCR, [qry_alm].UNIT_ID " & _[/FONT]
[FONT=Courier New]"FROM [qry_alm] " & _[/FONT]
[FONT=Courier New]"WHERE " & strDate & " AND (([qry_alm].UNIT_ID ='FC' Or " & [qry_alm].UNIT_ID ='FF' Or _[/FONT]
[FONT=Courier New]"[qry_alm].UNIT_ID ='FL' Or [/FONT][FONT=Courier New][qry_alm].UNIT_ID ='M3' Or [qry_alm].UNIT_ID ='AS') AND " & _[/FONT]
[FONT=Courier New]"([qry_alm].ALARM_PRIO Not Like 'LOW*')) [/FONT][FONT=Courier New]AND NOT ((qry_pPOS_rpt.POINT_NAME)='FCU2165')";[/FONT]

although I still prefer

[FONT=Courier New]strsql = "SELECT [qry_alm].ID_NO, [qry_alm].TIME_STAMP, " & _[/FONT]
[FONT=Courier New][qry_alm].SRC_NODE, [qry_alm].POINT_NAME, [qry_alm].ALARM_TYPE, " & _[/FONT]
[FONT=Courier New][qry_alm].ALARM_PRIO, [qry_alm].POINT_DSCR, [qry_alm].UNIT_ID " & _[/FONT]
[FONT=Courier New]"FROM [qry_alm] " & _[/FONT]
[FONT=Courier New]"WHERE " & strDate & [/FONT][FONT=Courier New]" AND (([qry_alm].UNIT_ID IN ('FC', 'FF', 'FL', 'M3', 'AS')) " & _[/FONT]
[FONT=Courier New]"AND ([qry_alm].ALARM_PRIO Not Like 'LOW*')) [/FONT][FONT=Courier New]AND NOT ((qry_pPOS_rpt.POINT_NAME)='FCU2165')";[/FONT]

 

[Pat Hartman]

There are several problems with the query but the most important one is that you have added a column from a table that is not in your FROM clause. Add the new table and include the proper join clause.

You can also eliminate the string of ORs by using the IN() construct -
[qry_alm].UNIT_ID IN('FC','FF','FL','M3','AS')

Doing this also eliminates a problem in the original string with misplaced parentheses that would cause the condition to be evaluated incorrectly.

And finally, this query is not dynamic and so should not be an embedded SQL String. You should use a querydef which will be more efficient.

 

[tech69]

Thanks

Got it sorted. Thanks to all of you!

I've taken your comments and suggestions on board and will be looking to improve the way this DB runs over the coming weeks.

Thanks again, its been most appreciated.