Error: You Tried To Execute A Query That Does Not Include The Specific Expression
Feb 28, 2008
I have a combobox iBrand on a form frmFoodSub with the following query used to get its values (based on the imput of another combobox, iProduct, linked to the field Product in the table Products.) This code was generated by query builder, not myself:
Code:SELECT Products.BrandFROM ProductsWHERE (((Products.Product)=[Me]![frmFoodSub].[form].[iProduct]))GROUP BY Products.BrandORDER BY Products.Product;
And I keep getting this error message when the combobox is in focus on my form:
You tried to execute a query that does not include the specific expression 'Products.Product' as part of the aggregated function.
I have no idea what started this error. It was all working fine and then all of a sudden the error started appearing and I can't remember making any changes to cause it.
A new query I am building, after entering an expression, gave the " You tried to execute a query that does not include the specified expression.." error. The new query is based on and uses expressions from another query. The error message listed the expressions from the query upon which this query was based. Copying in all the fields referenced in the error message did not work.
Here is the SQL code, the offending expression in red:
SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, [GPA]=Sum([Test Grade Results]![Wghtd41])/[Test Grade Results]![CuumWeightDivisor] AS Expr1FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumberGROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;
Wghtd41 is the expession that is included in the error message.
This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:
THIS GIVES ME NO ERRORS:
Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; When I make the following changes to the SELECT section, I get this error:
You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.
Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... Any insight is greatly appreciated.
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code: Call func1("Z", 8) Public Sub func1(b As String, a As Integer) strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _ & "FROM jabberwocky " _ & "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _ & "HAVING ScanDate=" & J & " and Scanhour=" & a & "" End SUb
This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null. I have ran the code below sucessfully. expr:dateadd("d", 1,[lastlandingslot])
How can I get this to execute only if the [lastduration] is not null??
I got the error message cannot execute command after we upgraded from Access 97 to Access 2000. There are multiple users connected to one database on Windows 2000. Some people are able to get access, but there is a good chunk of people that get the error message " Cannot execute Command".
All I want to do is run a make table query (or append) and show the user how many records were processed. If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".
I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.
strquery = "qryEmailGenerate" Set db = CurrentDb Set qdf = db.QueryDefs(strquery) Set rs = qdf.Execute txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
Hello, first time posting, not sure what info you all need. I'm looking for some help with an if/then statement in access query involving dates. Below is the query I am trying to use, along with the error message. Any ideas?
Domestic Violence CME Required?: IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")
syntax error (comma) in query expression 'IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")'
The error seems to be in relationship to the number 6. I want a "yes" to show up in the field if the difference is greater then 6 years.
Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.
Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));
What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078 The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
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)
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
I'm experiencing a error when I go to add a filter, "Syntax error in string in query expression "MyFieldName""..If I go to to the table where the field is located, I CAN apply a filter.However, If I throw this single field on a form and switch to datasheet view, all of a sudden I can not filter it.
Additional info: whenever I go to build a query with the field, it throws brackets around this field and no others... this is weird.
Code: SELECT MyTable.field1, MyTable.field2, MyTable.[problemfield] FROM MyTable
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'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
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 ..."
