How to use NOT Operator in Queries

[ataurrehman]

Dear All I am building a search form in access. I want my users to customize their search. So i have put AND OR and NOT operators in a combo box. AND and OR operators are working fine. But by using NOT operator returns a syntax error. What to do?
Query is as under:

select * from dtlsuggestions where (([Title] & ': ' & [Subtitle]) = 'Physics')
 AND (([AUTHOR] & ', ' & [SUBAUTHOR]) = 'David')
 OR (Subject = 'Quantum')
 AND (Publisher = 'IRVIN')
 NOT (([DDC_NO] & ' ' & [Auth_MARK]) = '658.2')

[FishVal]

Dear All I am building a search form in access. I want my users to customize their search. So i have put AND OR and NOT operators in a combo box. AND and OR operators are working fine. But by using NOT operator returns a syntax error. What to do?
Query is as under:

select * from dtlsuggestions where (([Title] & ': ' & [Subtitle]) = 'Physics')
 AND (([AUTHOR] & ', ' & [SUBAUTHOR]) = 'David')
 OR (Subject = 'Quantum')
 AND (Publisher = 'IRVIN')
 NOT (([DDC_NO] & ' ' & [Auth_MARK]) = '658.2')

NOT is unary logic operator, not binary like OR, AND.
it uses one operand only and reverses it's boolean value, e.g.
NOT False = True
NOT True = False

What are you expecting from expression like
Expr1 NOT Expr2 ?

[ataurrehman]

Dear FishVal
Thanks for response

If there are two filelds in tblabc, 'title' and 'author'. I want all titles having 'physics' But NOT author='david'

It will return 'Physics by vikram', 'physics by khan' but it will not return 'physics by david'

Is it possible?

Ata

NOT is unary logic operator, not binary like OR, AND.
it uses one operand only and reverses it's boolean value, e.g.
NOT False = True
NOT True = False

What are you expecting from expression like
Expr1 NOT Expr2 ?

[Twanne]

Hi,

I think this might be a solution to your problem:

SELECT * FROM dtlsuggestions WHERE title = "Physics" AND NOT author = "david";

This is the solution I think. If the title is longer than just physics you better use "like" to select the title.

Greetz
Twanne

Keeping the keys clean.

[FishVal]

Or simply

SELECT * FROM dtlsuggestions WHERE title = "Physics" AND author <> "david";

"NOT" is generally useful for those operators which don't have their logic opposites or for functions returning boolean values.

e.g.

Not In (SELECT ...)
Not objName Is Nothing
Not IsNull(varName)

[Kevin Wilcox]

I use variations on an 'Add to Where' function I found on the web many years back, which allows me to search strings. Here are some samples;

Function AddToWhere(Fiel dValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

Function AddaValue(Field Value As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " AND "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & "=" & FieldValue)

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

Function AddaStatement(F ieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " AND "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & FieldValue)

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

These variations determine whether you include 'AND' or 'LIKE' or '=' in the sql statement, and you could create a further function to include 'NOT' or 'NOT LIKE'.

Here's a sample of using these functions, an expert coder would no doubt cringe at some of this, but it works :-)

Private Sub TriageFilter()
On Error GoTo Proc_Err

Dim MySQL As String
Dim MyDB As Database
Dim qdfNew As QueryDef
Dim MyCriteria As String
Dim MyServiceCriter ia As String
Dim MyFilterCriteri a As String
Dim MyRecordSource As String
Dim MyServiceArgCou nt As Integer
Dim MyFilterArgCoun t As Integer
Dim Tmp As Variant

Set MyDB = CurrentDb

MyServiceArgCou nt = 0
MyFilterArgCoun t = 0

MySQL = "SELECT tblCases.CaseID , tblEvents.Actio nID, tblEvents.Servi ceID, tblEvents.Statu sID " & _
"FROM tblCases LEFT JOIN tblEvents ON tblCases.CaseID = tblEvents.CaseI D " & _
"WHERE (((tblEvents.Ac tionID)=35 Or (tblEvents.Acti onID)=44 Or (tblEvents.Acti onID)=24)) AND "

MyCriteria = ""


AddaStatement Me.cboFilter.Co lumn(0), "[StatusID]", MyFilterCriteri a, MyFilterArgCoun t
AddaValue Me.cboService.C olumn(0), "[ServiceID]", MyServiceCriter ia, MyServiceArgCou nt

If IsNull(Me.cboSe rvice) Then
MyCriteria = MyFilterCriteri a
ElseIf IsNull(Me.cboFi lter) Then
MyCriteria = MyServiceCriter ia
Else
MyCriteria = MyFilterCriteri a & " AND " & MyServiceCriter ia
End If

If MyCriteria = "" Then
MyCriteria = "True"
End If
MyRecordSource = MySQL & MyCriteria
' I sometimes put in a msgbox and exit sub here when developing the
' statement, so that I can see the sql and check it's what I wanted, e.g.
'msgbox MyRecordSource
'exit sub
If acbDoesObjExist ("qryTriageBase line", acQuery) Then
DoCmd.DeleteObj ect acQuery, "qryTriageBasel ine"
End If

With MyDB
Set qdfNew = .CreateQueryDef ("qryTriageBase line", MyRecordSource)
End With

Me.RecordSource = "select * from qryTriage;"

If Form.RecordsetC lone.RecordCoun t = 0 Then
Me.txtMessage = "THERE ARE NO CASES MATCHING YOUR SEARCH CRITERIA."
Me.txtRecordNo = "No Cases"
'Form.Requery
Else
Me.txtMessage = ""
End If
Form_Current
Form.Refresh

Set MyDB = Nothing
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "Oops, that must be error number " & Err.Number & ": " & Err.Description
Resume Proc_Exit

End Sub

tip: being a bit dense at times I sometimes can't visualise the resulting sql from the code, so I often put in a msgbox

[ataurrehman]

can you send me this application working?

Ata

I use variations on an 'Add to Where' function I found on the web many years back, which allows me to search strings. Here are some samples;

Function AddToWhere(Fiel dValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

Function AddaValue(Field Value As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " AND "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & "=" & FieldValue)

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

Function AddaStatement(F ieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " AND "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & FieldValue)

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Function

These variations determine whether you include 'AND' or 'LIKE' or '=' in the sql statement, and you could create a further function to include 'NOT' or 'NOT LIKE'.

Here's a sample of using these functions, an expert coder would no doubt cringe at some of this, but it works :-)

Private Sub TriageFilter()
On Error GoTo Proc_Err

Dim MySQL As String
Dim MyDB As Database
Dim qdfNew As QueryDef
Dim MyCriteria As String
Dim MyServiceCriter ia As String
Dim MyFilterCriteri a As String
Dim MyRecordSource As String
Dim MyServiceArgCou nt As Integer
Dim MyFilterArgCoun t As Integer
Dim Tmp As Variant

Set MyDB = CurrentDb

MyServiceArgCou nt = 0
MyFilterArgCoun t = 0

MySQL = "SELECT tblCases.CaseID , tblEvents.Actio nID, tblEvents.Servi ceID, tblEvents.Statu sID " & _
"FROM tblCases LEFT JOIN tblEvents ON tblCases.CaseID = tblEvents.CaseI D " & _
"WHERE (((tblEvents.Ac tionID)=35 Or (tblEvents.Acti onID)=44 Or (tblEvents.Acti onID)=24)) AND "

MyCriteria = ""


AddaStatement Me.cboFilter.Co lumn(0), "[StatusID]", MyFilterCriteri a, MyFilterArgCoun t
AddaValue Me.cboService.C olumn(0), "[ServiceID]", MyServiceCriter ia, MyServiceArgCou nt

If IsNull(Me.cboSe rvice) Then
MyCriteria = MyFilterCriteri a
ElseIf IsNull(Me.cboFi lter) Then
MyCriteria = MyServiceCriter ia
Else
MyCriteria = MyFilterCriteri a & " AND " & MyServiceCriter ia
End If

If MyCriteria = "" Then
MyCriteria = "True"
End If
MyRecordSource = MySQL & MyCriteria
' I sometimes put in a msgbox and exit sub here when developing the
' statement, so that I can see the sql and check it's what I wanted, e.g.
'msgbox MyRecordSource
'exit sub
If acbDoesObjExist ("qryTriageBase line", acQuery) Then
DoCmd.DeleteObj ect acQuery, "qryTriageBasel ine"
End If

With MyDB
Set qdfNew = .CreateQueryDef ("qryTriageBase line", MyRecordSource)
End With

Me.RecordSource = "select * from qryTriage;"

If Form.RecordsetC lone.RecordCoun t = 0 Then
Me.txtMessage = "THERE ARE NO CASES MATCHING YOUR SEARCH CRITERIA."
Me.txtRecordNo = "No Cases"
'Form.Requery
Else
Me.txtMessage = ""
End If
Form_Current
Form.Refresh

Set MyDB = Nothing
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "Oops, that must be error number " & Err.Number & ": " & Err.Description
Resume Proc_Exit

End Sub

tip: being a bit dense at times I sometimes can't visualise the resulting sql from the code, so I often put in a msgbox

[ataurrehman]

Dear Twanne and FishVal

Thanks. Now it is working

Ata

Or simply

SELECT * FROM dtlsuggestions WHERE title = "Physics" AND author <> "david";

"NOT" is generally useful for those operators which don't have their logic opposites or for functions returning boolean values.

e.g.

Not In (SELECT ...)
Not objName Is Nothing
Not IsNull(varName)

[Kevin Wilcox]

can you send me this application working?

Ata

I'm sorry, but not at the moment - I'm still developing the one this code came from, but happy to elaborate on the functions if it would help. The methods used in other posts look interesting, I may try these as an alternative to mine.