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')
-
-
-
8 7393
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 ?
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 ?
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.
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)
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(FieldValue 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(FieldValue 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(FieldValue 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 MyServiceCriteria As String
Dim MyFilterCriteria As String
Dim MyRecordSource As String
Dim MyServiceArgCount As Integer
Dim MyFilterArgCount As Integer
Dim Tmp As Variant
Set MyDB = CurrentDb
MyServiceArgCount = 0
MyFilterArgCount = 0
MySQL = "SELECT tblCases.CaseID, tblEvents.ActionID, tblEvents.ServiceID, tblEvents.StatusID " & _
"FROM tblCases LEFT JOIN tblEvents ON tblCases.CaseID = tblEvents.CaseID " & _
"WHERE (((tblEvents.ActionID)=35 Or (tblEvents.ActionID)=44 Or (tblEvents.ActionID)=24)) AND "
MyCriteria = ""
AddaStatement Me.cboFilter.Column(0), "[StatusID]", MyFilterCriteria, MyFilterArgCount
AddaValue Me.cboService.Column(0), "[ServiceID]", MyServiceCriteria, MyServiceArgCount
If IsNull(Me.cboService) Then
MyCriteria = MyFilterCriteria
ElseIf IsNull(Me.cboFilter) Then
MyCriteria = MyServiceCriteria
Else
MyCriteria = MyFilterCriteria & " AND " & MyServiceCriteria
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("qryTriageBaseline", acQuery) Then
DoCmd.DeleteObject acQuery, "qryTriageBaseline"
End If
With MyDB
Set qdfNew = .CreateQueryDef("qryTriageBaseline", MyRecordSource)
End With
Me.RecordSource = "select * from qryTriage;"
If Form.RecordsetClone.RecordCount = 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
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(FieldValue 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(FieldValue 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(FieldValue 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 MyServiceCriteria As String
Dim MyFilterCriteria As String
Dim MyRecordSource As String
Dim MyServiceArgCount As Integer
Dim MyFilterArgCount As Integer
Dim Tmp As Variant
Set MyDB = CurrentDb
MyServiceArgCount = 0
MyFilterArgCount = 0
MySQL = "SELECT tblCases.CaseID, tblEvents.ActionID, tblEvents.ServiceID, tblEvents.StatusID " & _
"FROM tblCases LEFT JOIN tblEvents ON tblCases.CaseID = tblEvents.CaseID " & _
"WHERE (((tblEvents.ActionID)=35 Or (tblEvents.ActionID)=44 Or (tblEvents.ActionID)=24)) AND "
MyCriteria = ""
AddaStatement Me.cboFilter.Column(0), "[StatusID]", MyFilterCriteria, MyFilterArgCount
AddaValue Me.cboService.Column(0), "[ServiceID]", MyServiceCriteria, MyServiceArgCount
If IsNull(Me.cboService) Then
MyCriteria = MyFilterCriteria
ElseIf IsNull(Me.cboFilter) Then
MyCriteria = MyServiceCriteria
Else
MyCriteria = MyFilterCriteria & " AND " & MyServiceCriteria
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("qryTriageBaseline", acQuery) Then
DoCmd.DeleteObject acQuery, "qryTriageBaseline"
End If
With MyDB
Set qdfNew = .CreateQueryDef("qryTriageBaseline", MyRecordSource)
End With
Me.RecordSource = "select * from qryTriage;"
If Form.RecordsetClone.RecordCount = 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
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)
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: William Gill |
last post by:
I am using the % operator to create queries for a db app. It works fine
when exact strings, or numbers are used, but some queries need partial
matching that use the '%' as a wildcards. So for...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: Mikhail |
last post by:
Hi ,
Does anybody know -how many elements could be in SQL operator IN?.
As an example DELETE FROM emp WHERE ename IN (NULL, 'king',...,N);
Thank you in advance .
|
by: Vlad |
last post by:
I operate with 2 tables: tbLocations and tbDepartments. Both have a column
named Town.
tbLocations.Town looks like long unique list of different towns world wide:
Copenhagen
Madrid
Roma
|
by: Roger Leigh |
last post by:
This code works (GCC 3.3.3):
#include <iostream>
int main()
{
bool a = true;
std::cout << "True and false\n"
<< (a == true) ? 't' : 'f';
std::cout << "\n";
return 0;
|
by: Scott Morford |
last post by:
I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the...
|
by: jayj0nes |
last post by:
I'm querying a Mas90 db via ADO in Access.
The following SQL gives me the error "Invalid Operand for Operator: ="
"SELECT * from GL_DetailPosting where PostingDate >= '#01/01/2004#'"
These other...
|
by: David Laub |
last post by:
I know there is no C# exponentiation operator. But since
the double class is sealed, there seems no way to add the
operator override without creating a new class which uses
containment (of a...
|
by: Sonu |
last post by:
Hi All,
How to use an IN operator using asp.net radio control list or checkbox list
control
For example...
Select * from <table name> where name IN ('james','Bob','Jhon')
how to use the...
|
by: xwero |
last post by:
Hello i looked in many fora and on many documentation websites but i can't find a way to use the in operator in a mysql query without having an error.
select name from users where id in (?)
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |