Can't understand why the following doesn't work when changing the operator.
Using the "<>"
SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;
the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))
it works if the operator is "=" but not "<>"
The thing is I'm sure this was working fine.
edit: I have just noticed that this works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.
I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)
I want a query to return all the results in the first table that are not equal to the second table
I have tried the following but it returns all 54 records Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));
I have tried changing the where clause to have it as Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);
but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records
I have a problem with a query. Just for info, I export some table from SQL to access. The same query in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------ Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'. ------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".
Example, the function will pass into the criteria the following: <150
Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.
I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.
Has anyone every handled a "variable" operator in a saved query in Access? :confused:
I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.
This overall method is really a great way to reuse criteria based on a form without hard coding.
I have a problem with a query. Just for info, I export some table from SQL to access. The same quary in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------ Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'. ------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I have a field that contains semester info ie: F07 Sep.
What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007
This is what I've tried and various combinations. Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","")
I am trying to select specific records based on input in a query. This criteria currently works to select everything with an ID less than 21 or 31 based on the users entry: <IIf([Enter "Y" for List 1]="Y",21,31)
I now need to change the query so that only certain records are selected based on the users entry. As an example, I would like to select 1, 3, 5 and 7 if they enter Y. And I would like to select 2, 4, 6 and 8 if they dont enter a Y.Here is what I have tried:
In IIf([Enter "Y" for List 1]="Y",(3,5,7,9),(2,4,6,8)).With this criteria, I get this error .The In operator you entered requires parentheses.
Morning all The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion" Any ideas on why it is showing this message box??? Help would be appricated Thank you
After adding a radio type field in the form called Blocco (with yes or no values, working like the existing field "Bozza") I've modified the code adding something to update the db:
The page itself is loaded with no errors but when the form takes action I get this: Syntax error (missing operator) in query expression 'True Articoli.Bozza = False'.
why? I've just used the same code that worked with the field "Bozza" (which is a checkbox field too)
Sub PickRandom() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim rst As DAO.Recordset Dim strSQL As String Dim strTableName As String
' 1: Create a new temporary table containing the required fields strSQL = "SELECT tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO tblTemp " & _ "FROM tblWoodrow Odom;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL (HERE I RECEIVE AN ERROR MESSAGE. WHAT I NEED TO DO HERE SO IT CAN RUN PROPERLY? ) DO I NEED TO DO A QUERY STRQSQL STATEMENT?) DoCmd.SetWarnings True
' 2: Add a new field to the new table Set db = CurrentDb() Set tdf = db.TableDefs("tblTemp") Set fld = tdf.CreateField("RandomNumber", dbSingle) tdf.Fields.Append fld
' 3: Place a random number in the new field for each record Set rst = db.OpenRecordset("tblTemp", dbOpenTable) rst.MoveFirst Do Randomize rst.Edit rst![RandomNumber] = Rnd() rst.Update rst.MoveNext Loop Until rst.EOF rst.Close Set rst = Nothing
' 4: Sort the data by the random number and move the top 25 into a new table strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy") strSQL = "SELECT TOP 25 tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO " & strTableName & " " & _ "FROM tblTemp " & _ "ORDER BY tblTemp.RandomNumber;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True
' 5: Delete the temporary table db.TableDefs.Delete ("tblTemp") End Sub
I've come across a simple error that has me baffled. I continue to receive the run time error 3075: Syntax error (missing operator) in query expression 'tblMasterPersonnel.FirstName"042" Or (tblMasterPersonnel.EmpID)="044"))'
Here is the strSQL
Code: strSQL = " SELECT tblMasterPersonnel.EmpID, qryiuSSN.SSN, ""SSN"" AS [ID Type], """" AS TXN, tblMasterPersonnel.LastName, " & _ "tblMasterPersonnel.FirstName, tblMasterPersonnel.MI, """" AS Suffix, IIf([tblMasterPersonnel].[Gender]=True,""F"",""M"") AS Gender, " & _ "tblMasterPersonnel.Birthday, tblMasterPersonnel.PlaceBirth, """" AS [Country Code], """" AS Country, tblAddresses.Phone1, " & _ "tblAddresses.Street, tblAddresses.City, tblAddresses.State, tblAddresses.Zip, tblAddresses.Country, " & _
SELECT Costs.Costs_Per_Capita, Costs.Period IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1), (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) AS result FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:
Syntax Error (Missing Operator) In (Field Name)
I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.
I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.
I have been refreshing myself with SQL on Access 2002. All was going well until I got to the BETWEEN...AND operator. Using the Northwind sample database I was writing the query on the employee table to select all the columns between 'Fuller' and 'King' :
SELECT * FROM Employees WHERE LastName BETWEEN 'Fuller' AND 'King'
However this only brings back the two rows for 'Fuller' and 'King' whereas I would expect at least 5 rows or more depending on whether access would count 'Fuller' and 'King' as inclusive. Have had a look on the microsoft office help page but seem to be doing everything correct so I am wondering why it still doesn't work? No doubt simple but its concealed in mud at the moment. Any help apreciated.
I am new to this forum, so forgive me if this question has been asked before
I needed exact information about the way the 'like' operator works, so I pressed F1, typed 'like' in the "Answer Wizard" and was told to "rephrase my question". :eek:
The index was slightly more helpful as it obviously contains 2 topics with 'like_operator', but choosing them does not reveal anything. :confused:
Where can I find info about such things? I also need to use an operator called 'contains' which I have used when working with SAS, but appearently it does not exist in Acccess or it has another name. Has anybody heard about it?
It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:
Table1
Book Agent ---------- b1 a1 b2 a2 b3 a3
Table2
Book Agent ---------- b1 a1 b3 a3
The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using Select * From Table1 Where (Book, Agent) Not In (Select Book, Agent From Table2) didn't work I also tried EXISTS didn't work Unmatched Wizard compares only one field, not two.
I have an invoice-line table with records for every line on invoices. I'm trying to return all the lines associated with certain invoices, excluding all lines with "FREIGHT" in a line item field. I created a query that lists all the invoice numbers for which I want to display invoice lines for. I created a new query with the invoice line table linked to the invoice number query by invoice number (a one to many relationship). There are about 1400 results from this query. By making the criteria of the line item field "FREIGHT" and running the query, I can see that there are 81 "FREIGHT" records that I ultimately want to exclude. However, by typing NOT "FREIGHT," it appears that not only are the "FREIGHT" line item records excluded, but also records with a blank (null)line item field are excluded, and I end up with about 650 results. What's going on here? Why is the NOT "FREIGHT" critera also excluding null records? I don't have any other criteria specified in the query. :confused:
I have an expression that I'm trying to build into a totaling field in a report. There are two types of items being tallied into this total; 1 is a flat amount, the other is a calculation of # of men * # hours * rate. I already have an expression that works for the first level of calculating:
=IIf([Type]="Lawn Maintenance",[LMAmt],[# hours]*[# men]*35) Note: [LMAmt] holds a flat amount
I wanted to add, after the [Type]="Lawn Maintenance",[LMAmt] an "or" somehow which would basically state or [Type]="Dumping Charge",[Expenses] Note: [Expenses] is also a flat amount and all other [Type] codes are determined using the calculation below:
if [Type] is not = to "Lawn Maintenance" or = to "Dumping Charge", THEN [#hours]*[# men]*35
